SQL SERVER 拼字符串方式动态表名游标变量范例

你的名字 2022-09-30 04:56 15阅读 0赞

存储过程1：

CREATE PROCEDURE \[dbo\].\[pro\_get\_cursor\]  
    @In\_TableName nvarchar(200),  
    @Out\_CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    declare @sql nvarchar(2000)  
    set @sql=N'DECLARE CurrencyCursor CURSOR  
          FORWARD\_ONLY STATIC FOR  
          SELECT \* FROM ['+@In\_TableName][In_TableName]  
    EXEC(@sql)  
    OPEN CurrencyCursor;  
    SET @Out\_CurrencyCursor = CurrencyCursor;

存储过程2：（调用存储过程1）

CREATE PROCEDURE \[dbo\].\[pro\_test\_c\]  
AS  
BEGIN  
DECLARE @MyCursor CURSOR;  
    declare @str1 varchar(100)  
    declare @str2 varchar(100)  
        declare @str3 varchar(100)  
    declare @str4 nvarchar(100)  
    declare @str5 nvarchar(100)  
    declare @i int=1  
    EXEC dbo.pro\_get\_cursor '表名',@Out\_CurrencyCursor = @MyCursor OUTPUT;

FETCH NEXT FROM @MyCursor into @str1,@str2,@str3,@str4,@str5  
WHILE (@@FETCH\_STATUS = 0)  
BEGIN;  
     print convert(nvarchar(5),@i)  
     set @i= @i+1  
     FETCH NEXT FROM @MyCursor into @str1,@str2,@str3,@str4,@str5  
END;

CLOSE @MyCursor;  
DEALLOCATE @MyCursor

\--注意此处： 调用存储过程1的游标的不能再次CLOSE,只能催毁对像。否则异常发生。

\--CLOSE CurrencyCursor  
DEALLOCATE CurrencyCursor

\--以下代码可以用来查看游标的列表

\--DECLARE @Report CURSOR

\-- Execute sp\_cursor\_list into the cursor variable.  
\--EXEC master.dbo.sp\_cursor\_list @cursor\_return = @Report OUTPUT,  
\--      @cursor\_scope = 3

\---- Fetch all the rows from the sp\_cursor\_list output cursor.  
\--FETCH NEXT from @Report  
\--WHILE (@@FETCH\_STATUS <> -1)  
\--BEGIN  
\--   FETCH NEXT from @Report  
\--END  
\--CLOSE @Report  
\--DEALLOCATE @Report

END

[In_TableName]: mailto:'%20rel=