CREATE PROCEDURE sp_GetNews
(
@CID varchar(100)
)
ASSELECT top 50 * FROM tblNews
WHERE charindex(','+convert(varchar(50), catid),(','+@CID))<>0
ORDER BY ID DESC
(
@CID varchar(100)
)
ASSELECT top 50 * FROM tblNews
WHERE charindex(','+convert(varchar(50), catid),(','+@CID))<>0
ORDER BY ID DESC
CREATE PROCEDURE sp_GetNews
(
@CID varchar(100)
)
AS
exec('SELECT top 50 * FROM tblNews WHERE catid in('+@CID+') ORDER BY ID DESC')GO
2.不使用动态sql语句,但将传入的字符串分隔到临时表。
这里有很多例子。就不详细写了。
(
@CID varchar(100)
)
AS
set nocount onexec ('SELECT top 50 * FROM tblNews WHERE catid in('+@CID+') ORDER BY ID DESC')GOexec sp_GetNews '483,199'
如果不在前后加“,”",1"和",10"区分不开的。
然后是=0.
如果不在前后加“,”",1"和",10"区分不开的。
然后是=0.
-------------------------------------------------------------------------
前后都加","也有问题的,
比如说现在的id是1,而@CID是1,10
这样的话也找不出正确结果的,
再如要是id是1,而@CID是10,1
这样也找不出结果
如果用字符串的话,存在很多弊端,所以还是用动态sql语句比较好
或者把@CID的值构造的时候就在前后都加上","这样就好区分了
AS
EXEC('SELECT top 50 * FROM tblNews WHERE catid in('+@CID+') ORDER BY ID DESC')
(
@CID varchar(100)
)
AS
set nocount onexec ('SELECT top 50 * FROM tblNews WHERE catid in('''+@CID+''') ORDER BY ID DESC')
--这里例如in ('483','199')两边的'号要自己加入,于是'''+@CID+''')GOexec sp_GetNews '483'',''199' --调用,传入字符串参数 '483'',''199',系统会去掉两边的'号,传入483','199, 其中''解释为一个',这样传入,执行字串最后解释为
SELECT top 50 * FROM tblNews WHERE catid in('483','199') ORDER BY ID DESC
CREATE PROCEDURE sp_GetNews
(
@CID varchar(100)
)
ASSELECT top 50 * FROM tblNews
WHERE charindex(','+catid+',',','+@CID+',')<>0
ORDER BY ID DESC/*
to caifen1985:
"比如说现在的id是1,而@CID是1,10
这样的话也找不出正确结果的,
再如要是id是1,而@CID是10,1
这样也找不出结果"
为什么不行?
*/
(
@CID varchar(100)
)
ASSELECT top 50 * FROM tblNews WHERE charindex(convert(varchar(50),catid),@CID)>0 ORDER BY ID DESC
SELECT top 50 * FROM tblNews WHERE charindex(convert(varchar(50),catid),@CID)>0 ORDER BY ID DESCif @CID=10 while catid value('1'), then you will get wrong result.
Meanwhile, [filebat(Mark)]'s solution is more perfect. The different is the delimiters ',' between catid and @CID, such as charindex(','+catid+',' , ','+@CID+',')<>0To [softj(天地客人)]
I agree with you. Maybe it's to be more perfect than others because of not converting @CID type to varchar.
[1] You can convert the varchar value(sprocs parameter) to data type int, such as:
--[softj(天地客人)]'s solution
--Implicit converting
exec('SELECT top 50 * FROM tblNews WHERE catid in('+@CID+') ORDER BY ID DESC')
[2] You also can convert the column of data type int to varchar, such as:
--Integration between [filebat(Mark)]'s solution and [QQMagicer()]'s
SELECT top 50 * FROM tblNews
WHERE charindex(','+convert(varchar(50),catid)+',',','+@CID+',')<>0
ORDER BY ID DESC