存储过程中的游标转换失败 [英] Conversion failed in cursor in stored procedure

问题描述

输入 111111 和 101,102,103,104

想要检查用户是否有权访问此请求...

I want to check whether user has access on this requests or not...

我尝试了一个游标如图所示，但我收到此错误：

I tried a cursor as shown, but I get this error:

将varchar值'101,102,103,104'转换为数据类型int时，转换失败。

Conversion failed when converting the varchar value '101,102,103,104' to data type int.

代码：

ALTER PROCEDURE [dbo].[ValidateRqstId]
    @UserID VARCHAR(50),
    @RsqtIDs VARCHAR(300)
AS
BEGIN
   Declare @RqstId int
   Declare @Result int
   Declare @UserIDToCheck VARCHAR(50)
   Declare @RqstUserVal cursor for  
               Select RequestId 
               from REQUEST_LIST  
               where RequestId in (@RsqtIDs)

   BEGIN

      OPEN RqstUserVal
      FETCH NEXT from RqstUserVal into @RqstId

      WHILE(@@fetch_status <> -1)
      BEGIN
        SET @UserIDToCheck = (
               select UserId from dbo.REQUEST_LIST where RequestId =  @RqstId)

        Print(@UserIDToCheck)

        If(@UserIDToCheck != @UserID)
            SET @Result = 99 ;

        --Fetch the next row from the cursor
        FETCH RqstUserVal into @RqstId
      END
   END

   CLOSE RqstUserVal
   Deallocate RqstUserVal

   RETURN @Result 
END

推荐答案

根据您的SQL-Server Verion，您可以使用表值函数， b
$ b

Depending on your SQL-Server Verion you can use a Table-Valued Function like in this short example

Select * from dbo.test1
Where ID in(
Select * from dbo.F_SplitAsIntTable('1,123,234,456'))

函数定义为

CREATE FUNCTION F_SplitAsIntTable 
(
@txt varchar(max)
)
RETURNS 
@tab TABLE 
(
 ID int
)
AS
BEGIN
    declare @i int
    declare @s varchar(20)
    Set @i = CHARINDEX(',',@txt)
    While @i>1
        begin
          set @s = LEFT(@txt,@i-1)
          insert into @tab (id) values (@s)
          Set @txt=RIGHT(@txt,Len(@txt)-@i)
          Set @i = CHARINDEX(',',@txt)
        end
    insert into @tab (id) values (@txt) 
    RETURN 
END
GO

这篇关于存储过程中的游标转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！