declare @sql varchar(8000)
set @sql='select * from [table] '+case when @style is not null then 'where style='+@style else '' end+case when @keys is not null then ' and keys like '+@keys else '' end
exec(@sql)
set @sql='select * from [table] '+case when @style is not null then 'where style='+@style else '' end+case when @keys is not null then ' and keys like '+@keys else '' end
exec(@sql)
解决方案 »
- 数据库差异备份还原先错误:先前地还原操作未指定WITH NORECOVERY或WITH STANDBY!
- 求助1个sql的问题,百分百结贴!
- SQL2005定时批量访问通过WEB处理数据,应如何做?
- 求一个比较难的视图,请教高手!
- CSDN节贴存储过程讨论
- 这个问题是不是要用的触发器?
- 在本机企业管理器查询大量数据 报错"ODBC SQL Server Driver 超时已过期" 如何解决 谢谢
- 一个关于游标的简单问题
- 在线等待,win98下安装sqlserver2000为什么服务器管理器内有服务器名称没有服务?
- vfp6.0中的通用型字段?
- 100分跪求各位大虾
- 向表中添加数据时老是出现为空的问题?
select @style=isnull(@style,''),@keys=isnull(@keys,'')
declare @sql varchar(8000)
set @sql='select * from [table] '+case when @style<>'' then 'where style='+@style else '' end+case when @keys<>'' and @style<>'' then ' and keys like '+@keys when @keys<>'' and @style='' then 'where keys like '+@keys else '' end
exec(@sql)
declare @sql varchar(8000)
set @sql='select * from [table] '+case when @style is not null then 'where style='+@style else '' end+case when @keys is not null then ' and keys like '+@keys else '' end
exec(@sql)
---------------------------------------------------
不管style和keys有不有时都出错
把你的代码和错误贴出来看看.
有没有table这个表啊?
列名 'csqj' 无效。
declare @style char(2)
select @style='b'
declare @keys char(2)
select @keys='b'
set @sql='select * from table '+
case
when @style is null and @keys is null
then ''
else
'where '
end
+
case
when @style is not null
then 'style='''+@style+''' and '
end
+
case
when @keys is not null
then 'keys='''+@keys+''''
end
print @sql
我刚试过这个,借用了下,呵呵~~试试吧
declare @style char(2)
select @style='b'
declare @keys char(2)
select @keys='b'
set @sql='select * from table '+
case
when @style is null and @keys is null
then ''
else
'where '
end
+
case
when @style is not null
then 'style='''+@style+''' and '
end
+
case
when @keys is not null
then 'keys='''+@keys+''''
endprint @sql
例:
declare @style varchar(20)
set @style='aaa'
declare @keys int
set @keys=100
select @style=isnull(@style,''),@keys=isnull(@keys,0)
declare @sql varchar(8000)
set @sql='select * from [table] '+case when @style<>'' then 'where style='''+@style+'''' else '' end+case when @keys<>0 and @style<>'' then ' and keys = '+rtrim(@keys) when @keys<>0 and @style='' then 'where keys = '+rtrim(@keys) else '' end
exec(@sql)
请问一下,为什么要加那么多引号,还有当为like时引号要怎么写?
难怪老兄用了isnull()函数,长见识了!
declare @sql varchar(100)
declare @style char(1)
set @style='b'
declare @keys char(1)
set @keys='b'
set @sql='select * from table '+
case
when @style is null and @keys is null
then ''
else
'where '
end
+
case
when @style is not null
then 'style='''+rtrim(@style)+''' and '
else ''
end
+
case
when @keys is not null
then 'keys like '''+rtrim(@keys)+''''
else ''
end
print @sql
declare @sql varchar(100)
declare @style char(1)
declare @keys char(1)
set @style='a'
set @keys='b'
set @sql='select * from table '+
case
when @style is not null
then
'where style='''+rtrim(@style)+''''
else ''
end
+
case
when @style is not null and @keys is not null
then
' and keys like '''+rtrim(@keys)+''''
else ''
end
+
case
when @style is null and @keys is not null
then
'keys like '''+rtrim(@keys)+''''
else ''
end
print @sql
好像可以了。多谢了。
请问一下,为什么要加那么多引号,还有当为like时引号要怎么写?
----------------------------------------------------declare @style varchar(20)
set @style='aaa'
declare @keys varchar(20)
set @keys='xyz'
select @style=isnull(@style,''),@keys=isnull(@keys,'')
declare @sql varchar(8000)
set @sql='select * from [table] '+case when @style<>'' then 'where style='''+@style+'''' else '' end+case when @keys<>'' and @style<>'' then ' and keys like '''+@keys+'''' when @keys<>'' and @style='' then 'where keys like '''+@keys+'''' else '' end
exec(@sql)