我在存储过程中写的语句,@Name输入不管正确还是不正确的值都查不出来,大家帮我看看那里有问题。
set @sql='select * from Person_stuff where 1=1'
+CASE WHEN @Name !="" THEN 'and name='''+@Name+''' 'else "" END
set @sql='select * from Person_stuff where 1=1'
+CASE WHEN @Name !="" THEN 'and name='''+@Name+''' 'else "" END
+CASE WHEN isnull(@Name,'') <> '' THEN ' and name='''+@Name+'''' else '' END
set @name='a'
set @sql='select * from Person_stuff where 1=1 '
+CASE WHEN isnull(@Name,'') <> '' THEN ' and name='''+@Name+'''' else '' END
print @sql
/*
select * from Person_stuff where 1=1 and name='a'
*/set @name=null
set @sql='select * from Person_stuff where 1=1 '
+CASE WHEN isnull(@Name,'') <> '' THEN ' and name='''+@Name+'''' else '' END
print @sql
/*
select * from Person_stuff where 1=1
*/set @name=''
set @sql='select * from Person_stuff where 1=1 '
+CASE WHEN isnull(@Name,'') <> '' THEN ' and name='''+@Name+'''' else '' END
print @sql
/*
select * from Person_stuff where 1=1
*/测试没问题,再有问题是你那里的问题了。
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(4))
insert [tb]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'刘备'--------------------------------查询开始------------------------------declare @sql varchar(100),@Name varchar(20)
set @name='张三'
set @sql='select * from tb where 1=1 '
+CASE WHEN isnull(@Name,'') <> '' THEN ' and name='''+@Name+'''' else '' END
exec(@sql)
/*
id name
----------- ----
1 张三(1 行受影响)*/