- 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 ([email protected] AND [email protected]) OR (rid IN (@rids) AND [email protected]) IF @TempID = 0 RETURN 0 ELSE RETURN 1
这是一个错误的版本，出现的错误是
- SQL code
消息 245，级别 16，状态 1，过程 T_Menu_User_Role_ExistsByMenuAll，第 14 行在将 nvarchar 值 '1,2' 转换成数据类型 int 时失败。
[email protected] 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 ([email protected]+' AND [email protected]+') OR (rid IN (ltrim(@rids)) AND [email protected]+') 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 ([email protected] AND [email protected]) OR (CHARINDEX(',' + rtrim(rid) + ',',',' + @rids + ',') > 0 AND [email protected]) 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 ([email protected] AND [email protected]) OR (rid IN (' + @rids + ') AND [email protected])' EXEC SP_EXECUTESQL @SQL,[email protected] 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 ([email protected] AND [email protected]) OR (rid IN (ltrim(@rids)) AND [email protected]) -- 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 ([email protected] AND [email protected]) OR (rid IN ('' + @rids + '') AND [email protected])' execute sp_executesql @strSql ,[email protected] int output,@aid int,@mid int,@other1 int',@TempID output ,@aid,@mid,@other1 IF @TempID = 0 RETURN 0 ELSE RETURN 1