开发者

Dynamic IN with cursor

I have a dynamic in statement in cursor and in contains a collection of strings:

DECLARE @DBs n开发者_开发百科varchar(4000)
    SET  @DBs="'aaa','bbb','ccc','ddd'"

DECLARE CompanyDBCursor CURSOR FOR 
  SELECT Interid 
    FROM tableName
   WHERE interid  in (@DBs)

but it appears an error - Invalid ColumnName


Try this:

DECLARE @DBs varchar(8000)
        SET  @DBs='aaa,bbb,ccc,ddd'


DECLARE CompanyDBCursor CURSOR FOR 
  SELECT Interid 
    FROM tableName
   WHERE interid  in (SELECT * FROM dbo.SPLIT(@DBs,','))

Here is the table valued function:

CREATE FUNCTION [dbo].[SPLIT] (
    @str_in VARCHAR(8000),
    @separator VARCHAR(4)
)
RETURNS @strtable TABLE (strval VARCHAR(8000))
AS
BEGIN
    DECLARE @Occurrences INT, @Counter INT, @tmpStr VARCHAR(8000)
    SET @Counter = 0
    IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator 
        SET @str_in = @str_in + @separator

    SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
    SET @tmpStr = @str_in
    WHILE @Counter <= @Occurrences 
    BEGIN
        SET @Counter = @Counter + 1
        INSERT INTO @strtable VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))
        SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)
        IF DATALENGTH(@tmpStr) = 0
            BREAK
    END
    RETURN 
END
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜