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

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

SQL code

------------------------------------ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]@aid int,@rids nvarchar(200),@mid int,@other1 intAS    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

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

SQL code

消息 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拼接上也行，我应该怎么写呢！在线等回答，谢谢大家了啊！

[解决办法]

SQL code

ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]@aid int,@rids nvarchar(200),@mid int,@other1 intAS    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
[解决办法]
SQL code
ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]@aid int,@rids nvarchar(200),@mid int,@other1 intAS    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
[解决办法]
SQL code
ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]@aid int,@rids nvarchar(200),@mid int,@other1 intAS    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
[解决办法]
SQL code
if object_id('T_Menu_User_Role_ExistsByMenuAll') is not nulldrop PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]goCREATE PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]@aid int,@rids nvarchar(200),@mid int,@other1 intAS    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
[解决办法]
SQL code
ALTER PROCEDURE [dbo].[T_Menu_User_Role_ExistsByMenuAll]@aid int,@rids nvarchar(200),@mid int,@other1 intAS    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 

 

查看更多 下一篇