CREATE PROCEDURE sp_qrydepartment @code char(10), @name char(30) ASselect [code], [name] from department where code like @code and name like @nameGO CREATE TABLE [dbo].[department] ( [code] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [name] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] GOALTER TABLE [dbo].[department] WITH NOCHECK ADD CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED ( [code] ) ON [PRIMARY] GOALTER TABLE [dbo].[department] ADD CONSTRAINT [IX_department] UNIQUE NONCLUSTERED ( [name] ) ON [PRIMARY] GO insert [dbo].[department] select '1','2' union all select '3','4'exec sp_qrydepartment '%', '%' drop procedure sp_qrydepartment drop table [dbo].[department]code name ---------- ------------------------------ 1 2 3 4 (所影响的行数为 2 行)应该可以的!
难道用char类型就没有办法了吗?
小弟初学,为啥传入参数一定要定义成VARCHAR型的呢?
char是定长的再翻翻在线帮助吧,基础很重要
--用char型在%后边会填充空格至定义的长度,所以会查不到数据,如果一定要用char型,可以这样CREATE PROCEDURE sp_qrydepartment @code char(10), @name char(30) ASselect [code], [name] from department where code like rtrim(@code) and name like rtrim(@name)GO
是语法的逻辑有错误 在执行sp_qrydepartment '%', '%'时(传入的参数是(%,%)) 实际上是执行 select [code], [name] from department where code like % and name like %而不是你逻辑需要的 select [code], [name] from department where code like '%' and name like '%'你应该执行 sp_qrydepartment ''''+'%'+'''', ''''+'%'+''''才对啊 这时传入的参数才是('%','%')
@code char(10),
@name char(30)
ASselect [code], [name] from department
where code like @code
and name like @nameGO
CREATE TABLE [dbo].[department] (
[code] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[name] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[department] WITH NOCHECK ADD
CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED
(
[code]
) ON [PRIMARY]
GOALTER TABLE [dbo].[department] ADD
CONSTRAINT [IX_department] UNIQUE NONCLUSTERED
(
[name]
) ON [PRIMARY]
GO
insert [dbo].[department]
select '1','2' union all
select '3','4'exec sp_qrydepartment '%', '%'
drop procedure sp_qrydepartment
drop table [dbo].[department]code name
---------- ------------------------------
1 2
3 4 (所影响的行数为 2 行)应该可以的!
@code char(10),
@name char(30)
ASselect [code], [name] from department
where code like rtrim(@code)
and name like rtrim(@name)GO
在执行sp_qrydepartment '%', '%'时(传入的参数是(%,%))
实际上是执行
select [code], [name] from department
where code like %
and name like %而不是你逻辑需要的
select [code], [name] from department
where code like '%'
and name like '%'你应该执行 sp_qrydepartment ''''+'%'+'''', ''''+'%'+''''才对啊
这时传入的参数才是('%','%')