declare @kind varchar(30) declare @key varchar(400) declare @sql nvarchar(4000) set @kind='name' set @key='张三' SET @sql='SELECT * FROM [stuInfo]' if @kind<>'' SET @sql=@sql+' WHERE ('+@kind+'='''+@key+''')' exec(@sql) --说明:只适用于字符串字段查询
exec('SELECT * FROM [stuInfo] WHERE '+ @kind + ' = '+ @key)
declare @kind int,@key int select @kind =1,@key=1 SELECT * FROM sysobjects WHERE (@kind = @key) --这样是不会出错的go declare @kind nvarchar(2),@key int--两个变量的类型不一致 select @kind ='a',@key=1 SELECT * FROM sysobjects WHERE (@kind = @key) --这样是会出错的 ---传列名时go declare @kind nvarchar(2),@key int--两个变量的类型不一致 select @kind ='ID',@key=1 declare @s nvarchar(1000) set @s='select * from sysobjects where '+@kind+'='+rtrim(@key) exec(@s)
---传列名时 declare @kind nvarchar(20),@key nvarchar(20) select @kind ='Name',@key='sysobjects' declare @s nvarchar(1000) set @s='select * from sysobjects where '+@kind+'='''+@key+''''--字符时加上引号 exec(@s)
SELECT * FROM [stuInfo] WHERE (@kind = @key)我用 vs2005的sql server 2005写的. SELECT * FROM [stuInfo] WHERE ([name] = @key)这样就可以查到结果. (name是表里面的字段) 但是把它写成 @kind 再赋值 name 就是找不到
where @kind=@key sql server会把@kind 与@key都当成变量,而不会将@kind当成字段 要应动态的字段,应用动态生成语句,1,3楼的方法都行.
写个示例看看:use northwind declare @kind varchar(50), @key varchar(50), @str varchar(100)set @kind='customerid' set @key='ALFKI' set @str='SELECT * FROM [customers] WHERE (' + @kind +'= ''' + @key+''')' print @STR exec (@str)
-- testproc 'wpwp','wpwp' alter proc testproc @s varchar(10), @ss varchar(10) as begin select * from tb where @s=@ss end 可以这么用啊 楼主请看~~~~
你声明一样的类型的变量@kind,@key 了吗!2005可以这样查询的!declare @kind int declare @key int SELECT * FROM [stuInfo] WHERE @kind=@key
declare @kind varchar(30)
declare @key varchar(400)
declare @sql nvarchar(4000)
set @kind='name'
set @key='张三'
SET @sql='SELECT * FROM [stuInfo]'
if @kind<>''
SET @sql=@sql+' WHERE ('+@kind+'='''+@key+''')'
exec(@sql) --说明:只适用于字符串字段查询
exec('SELECT * FROM [stuInfo] WHERE '+ @kind + ' = '+ @key)
select @kind =1,@key=1
SELECT * FROM sysobjects WHERE (@kind = @key) --这样是不会出错的go
declare @kind nvarchar(2),@key int--两个变量的类型不一致
select @kind ='a',@key=1
SELECT * FROM sysobjects WHERE (@kind = @key) --这样是会出错的
---传列名时go
declare @kind nvarchar(2),@key int--两个变量的类型不一致
select @kind ='ID',@key=1
declare @s nvarchar(1000)
set @s='select * from sysobjects where '+@kind+'='+rtrim(@key)
exec(@s)
declare @kind nvarchar(20),@key nvarchar(20)
select @kind ='Name',@key='sysobjects'
declare @s nvarchar(1000)
set @s='select * from sysobjects where '+@kind+'='''+@key+''''--字符时加上引号
exec(@s)
要应动态的字段,应用动态生成语句,1,3楼的方法都行.
declare @kind varchar(50),
@key varchar(50),
@str varchar(100)set @kind='customerid'
set @key='ALFKI'
set @str='SELECT * FROM [customers] WHERE (' + @kind +'= ''' + @key+''')'
print @STR
exec (@str)
alter proc testproc
@s varchar(10),
@ss varchar(10)
as
begin
select * from tb where @s=@ss
end
可以这么用啊
楼主请看~~~~
declare @key int
SELECT * FROM [stuInfo] WHERE @kind=@key
declare @kind int
declare @key int
Select @kind=1,@key=1
SELECT * FROM [stuInfo] WHERE @kind=@key当两个变量值相同时,显示所有记录;否则不任何显示记录
不过这样做似乎没有什么意义!
如果是字段列名,就只好用动态的sql了
楼上的大神们都解决了!
仰视楼上的星星!