日期:2014-05-18  浏览次数:20633 次

一个简单的SQL语句问题请高手帮忙解决一下,在线等回答
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] 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 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

------解决方案--------------------
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] 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 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

------解决方案--------------------
SQL code
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

------解决方案--------------------
SQL code


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