declare @str char(80)
set @str='''10000008'',''100000129'',''10008700'',''10000503'''
print @str
set @str='select * from test where badge in ('+@str+')'
exec(@str)
set @str='''10000008'',''100000129'',''10008700'',''10000503'''
print @str
set @str='select * from test where badge in ('+@str+')'
exec(@str)
set @str='''10000008'',''100000129'',''10008700'',''10000503'''
print @str
select * from test where charindex(badge,@str)>0
set @str='''10000008'',''100000129'',''10008700'',''10000503'''
print @str
select * from test where charindex(''''+badge+'''',@str)>0
Line 1: Incorrect syntax near '10000503'.
Create Table TEST
(ID Int,
badge Varchar(10))
--插入数据
Insert TEST Values(1,'10000008')
Insert TEST Values(2,'10000009')
Insert TEST Values(3,'10008700')
--测试
declare @str char(80)
set @str='''10000008'',''100000129'',''10008700'',''10000503'''
--print @str
--方法一
exec('SELECT * from test Where badge in ('+@str+') ')
--方法二
select * from TEST where ','+@str+',' Like '%,'''+badge+''',%'
--方法三
Select * from TEST Where CharIndex(','''+ badge+''',',','+@str+',') >0
--删除测试环境
Drop Table TEST
--结果
/*
ID badge
1 10000008
3 10008700
*/
我試過了, 執行成功,謝謝了!
方法二好有創意啊.但是,方法一有個問題:
為什麼把查詢語句直接寫在exec()裏面就執行成功,
像lwla2寫的那樣,賦給一個變量就老是不行呢?
像lwla2寫的那樣,賦給一個變量就老是不行呢?謝謝
看了你就知道了
set @str='''10000008'',''100000129'',''10008700'',''10000503'''
--print @str
set @str='select * from test where badge in ('+@str+')'
exec(@str)
--结果
/*
ID badge
1 10000008
3 10008700
*/
declare @str char(300)
declare @str varchar(300)