在sql2005的,如何使用带逗号的字符串变量?
实例如下:
declare @instanceSerNos varchar(8000);
set @instanceSerNos=fx()
得到的结果是:@instanceSerNos='VC-2007-03-0010','VC-2007-03-0011'
想利用这个结果进行查询
select * from tb where 编号 in (instanceSerNos);
类似于
select * from tb where 编号 in ('VC-2007-03-0010','VC-2007-03-0011');
但发现是无效的查询,请教如何解决?谢谢
实例如下:
declare @instanceSerNos varchar(8000);
set @instanceSerNos=fx()
得到的结果是:@instanceSerNos='VC-2007-03-0010','VC-2007-03-0011'
想利用这个结果进行查询
select * from tb where 编号 in (instanceSerNos);
类似于
select * from tb where 编号 in ('VC-2007-03-0010','VC-2007-03-0011');
但发现是无效的查询,请教如何解决?谢谢
set @instanceSerNos = 'VC-2007-03-0010,VC-2007-03-0011'
set @instanceSerNos = '''' + replace(@instanceSerNos , ',' , ''',''') + ''''
print (@instanceSerNos)/*
'VC-2007-03-0010','VC-2007-03-0011'
*/
set @instanceSerNos = 'VC-2007-03-0010,VC-2007-03-0011' select * from tb where au_id 编号 ('''' + replace(@instanceSerNos , ',' , ''',''') + '''')
代入下式仍然无效
select * from tb where 编号 in (instanceSerNos);
declare @instanceSerNos as varchar(50)
set @instanceSerNos = 'VC-2007-03-0010,VC-2007-03-0011' select * from tb where 编号 ('''' + replace(@instanceSerNos , ',' , ''',''') + '''')
select * from tb where 实例编号 in ('VC-2007-03-0010','VC-2007-03-0011')
换了变量就不行
CREATE TABLE #tb(
[编号] [varchar](100)
)
insert into #tb(编号)values('VC-2007-03-0010')
insert into #tb(编号)values('VC-2007-03-0011')
insert into #tb(编号)values('VC-2007-03-0012')
insert into #tb(编号)values('VC-2007-03-0013')
declare @instanceSerNos varchar(8000);
set @instanceSerNos = 'VC-2007-03-0010,VC-2007-03-0011'
select * from #tb where [编号]in ('''' + replace(@instanceSerNos , ',' , ''',''') + '''')
select * from #tb where [编号]in ('VC-2007-03-0010','VC-2007-03-0011')
drop table #tb
-- 用就量为空,直接用数据得出下列结果
-- VC-2007-03-0010
-- VC-2007-03-0011
set @sql='select * from tb where 编号 in ('+@instanceSerNos+')'
exec(@sql)
如果不行就用动态SQL.
--我这样测试的,还是不行:
CREATE TABLE #tb(
[编号] [varchar](100)
)
insert into #tb(编号)values('VC-2007-03-0010')
insert into #tb(编号)values('VC-2007-03-0011')
insert into #tb(编号)values('VC-2007-03-0012')
insert into #tb(编号)values('VC-2007-03-0013')
declare @instanceSerNos varchar(8000);
set @instanceSerNos = 'VC-2007-03-0010,VC-2007-03-0011'
set @instanceSerNos = '''' + replace(@instanceSerNos , ',' , ''',''') + ''''
--在这里用动态SQL。
exec('select * from #tb where [编号] in (' + @instanceSerNos + ')')
/*
编号
----------------------------------------------------------------------------------------------------
VC-2007-03-0010
VC-2007-03-0011
*/
select * from #tb where [编号]in ('VC-2007-03-0010','VC-2007-03-0011')
/*
编号
----------------------------------------------------------------------------------------------------
VC-2007-03-0010
VC-2007-03-0011(所影响的行数为 2 行)
*/drop table #tb
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num