一个简单的SQL语句问题请高手帮忙解决一下，在线等回答

[云中的Angel]

------------------------------------
ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@mid int,
@other1 int
AS
	DECLARE @TempID int
	SELECT @TempID = count(1) FROM [T_Menu_User_Role] WHERE (aid=@aid AND other1=@other1) OR (rid IN (@rids) AND other1=@other1)
	IF @TempID = 0
		RETURN 0
	ELSE
		RETURN 1

这是一个错误的版本，出现的错误是

消息 245，级别 16，状态 1，过程 T_Menu_User_Role_ExistsByMenuAll，第 14 行
在将 nvarchar 值 '1,2' 转换成数据类型 int 时失败。

问题出现在@rids nvarchar(200)这个参数上面，rid IN (@rids)把这个当成了一个数，@rids实际是一个id的集合比如：rid IN (1，3，5，8) 应该是这样的，求高手应该怎么解决，或者用sql拼接上也行，我应该怎么写呢！在线等回答，谢谢大家了啊！

[--小F--]

ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@mid int,
@other1 int
AS
    DECLARE @TempID int
    SELECT @TempID = count(1) FROM [T_Menu_User_Role] WHERE (aid='+@aid+' AND other1='+@other1+') OR (rid IN (ltrim(@rids)) AND other1='+@other1+')
    IF @TempID = 0
        RETURN 0
    ELSE
        RETURN 1

[孤独加百列]

ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@mid int,
@other1 int
AS
    DECLARE @TempID int
    SELECT @TempID = count(1) FROM [T_Menu_User_Role] WHERE (aid=@aid AND other1=@other1) OR (CHARINDEX(',' + rtrim(rid) + ',',',' + @rids + ',') > 0 AND other1=@other1)
    IF @TempID = 0
        RETURN 0
    ELSE
        RETURN 1

[昵称被占用了]

ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@mid int,
@other1 int
AS
    DECLARE @TempID int
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = N'
    SELECT @TempID = count(1) FROM [T_Menu_User_Role] WHERE (aid=@aid AND other1=@other1) OR (rid IN (' + @rids + ') AND other1=@other1)'
    EXEC SP_EXECUTESQL @SQL,'@aid int,@other1 int,@TempID INT OUTPUT',@aid,@other1 ,@TempID OUTPUT
    IF @TempID = 0
        RETURN 0
    ELSE
        RETURN 1

[--小F--]

if object_id('T_Menu_User_Role_ExistsByMenuAll') is not null
drop PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
go
CREATE PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@mid int,
@other1 int
AS
    DECLARE @TempID int
    SELECT @TempID = count(1) FROM [T_Menu_User_Role] WHERE (aid=@aid AND other1=@other1) OR (rid IN (ltrim(@rids)) AND other1=@other1)
  -- print @tempid
    IF @TempID = 0
        RETURN 0
    ELSE
        RETURN 1

[天-笑]

ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@mid int,
@other1 int
AS
    DECLARE @StrSql nvarchar(1024)
    DECLARE @TempID int
    set @strSql = 'SELECT @TempID = count(1) FROM [T_Menu_User_Role] WHERE (aid=@aid AND other1=@other1) OR (rid IN ('' + @rids + '') AND other1=@other1)
' 
    execute sp_executesql @strSql ,N'@TempID int output,@aid int,@mid int,@other1 int',@TempID output ,@aid,@mid,@other1
   
    IF @TempID = 0
        RETURN 0
    ELSE
        RETURN 1

[云中的Angel]

[Quote=引用 1 楼 的回复:]

SQL code
ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@mid int,
@other1 int
AS
DECLARE @TempID int
SELECT @TempID = count(1) FROM [T_Menu_User_Role……
[/Quote]
这个还是报错啊，消息 245，级别 16，状态 1，过程 T_Menu_User_Role_ExistsByMenuAll，第 14 行
在将 varchar 值 '+@aid+' 转换成数据类型 int 时失败。

[云中的Angel]

怎么都不行呢，我郁闷了啊！

[孤独加百列]

[Quote=引用 7 楼 的回复:]

怎么都不行呢，我郁闷了啊！
[/Quote]

2楼的报什么错？

[云中的Angel]

ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@other1 int
AS
    DECLARE @StrSql nvarchar(1024)
    DECLARE @TempID int
    set @strSql = 'SELECT @TempID = count(1) FROM [T_Menu_User_Role] WHERE (aid=@aid AND other1=@other1) OR (rid IN (@rids) AND other1=@other1)' 
    execute sp_executesql @strSql ,N'@TempID int output,@aid int,@rids nvarchar,@other1 int',@TempID output ,@aid,@rids,@other1
   
    IF @TempID = 0
        RETURN 0
    ELSE
        RETURN 1

最后居然是这个样子的晕了！

[孤独加百列]

[Quote=引用 9 楼 的回复:]

SQL code

ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
@aid int,
@rids nvarchar(200),
@other1 int
AS
DECLARE @StrSql nvarchar(1024)
DECLARE @TempID int
set @strSql = 'SELECT ……
[/Quote]
2楼的我试过了可以的啊。。。

[云中的Angel]

[Quote=引用 8 楼 的回复:]

引用 7 楼 的回复:

怎么都不行呢，我郁闷了啊！


2楼的报什么错？
[/Quote]
不好意思刚才没有看到你的，的确很强大啊，十分感谢！

[Felixzhaowenzhong]

[Quote=引用 7 楼 的回复:]
怎么都不行呢，我郁闷了啊！
[/Quote]

ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]
	@aid int,
	@rids varchar(200),
	@mid int,
	@other1 int
AS
    DECLARE @TempID int
    DECLARE @SQL NVARCHAR(MAX)
    Declare @count int
    SET @SQL = N'SELECT @TempID = count(1) FROM [T_Menu_User_Role] WHERE (aid=@aid AND other1=@other1) OR (rid IN (' + @rids + ') AND other1=@other1)'
    EXEC SP_EXECUTESQL @SQL,N'@aid int,@other1 int,@TempID INT OUTPUT',@aid,@other1 ,@TempID OUTPUT
    IF @TempID = 0
        RETURN 0
    ELSE
        RETURN 1

 

[云中的Angel]

感谢大家，结贴给分，都辛苦了啊！