在SQL SERVER 2005中, 可以将一个整型值的集合赋给一个SQL的变量吗?
如果可以,该变量应定义成什么类型?
如有如下整型集合 (1,2,3,4)可以定义一个变量,并且将(1,2,3,4)赋给该变量吗?应用场景:IF @a = 0
t.FBillTypeID In (1,2,3,4)
ELSE IF @a = 1
t.FBillTypeID In (5,6,7,8)
ELSE IF @a = 2
t.FBillTypeID In (1,2,3,4,5,6,7,8)上面的整型集合能用变量代替吗?
如果可以,该变量应定义成什么类型?
如有如下整型集合 (1,2,3,4)可以定义一个变量,并且将(1,2,3,4)赋给该变量吗?应用场景:IF @a = 0
t.FBillTypeID In (1,2,3,4)
ELSE IF @a = 1
t.FBillTypeID In (5,6,7,8)
ELSE IF @a = 2
t.FBillTypeID In (1,2,3,4,5,6,7,8)上面的整型集合能用变量代替吗?
declare @s varchar(20)
set @s='1,2,3,4'
select * from tb where id in('+@s+')
declare @s varchar(20)
set @s='1,2,3,4'
select * from tb where id in('+@s+')
SET @idlist='1,2,3'
EXEC('SELECT * FROM tbname WHERE fdname IN('+@idlist+')')
GO
declare @s as varchar(20)
set @s = '1,2,3,4'
select ... from tb where charindex(','+cast(FBillTypeID as varchar) + ',', ','+@s+',') > 0
在将 varchar 值 '1,2,3,4,11,12,13,14' 转换成数据类型 smallint 时失败。
set @s = '1,2,3,4'
select * from tb where charindex(','+ltrim(FBillTypeID) + ',', ','+@s+',') > 0
set @s='(1,2,3,4)'
declare @sql varchar(50)
set @sql='select * from table where id in '+@s
print @sql
exec (@sql)