How to name a cursor dynamically in SQL Server

Question

Can anybody please tell how to declare a cursor name dynamically (fixed name + unique name) in SQL Server?

This is to prevent the error

02-25-2018 10:12:01 ERROR (AdHocReportserviceImpl.java:882) : org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call usp_AdHocReport(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}];
SQL state [34000]; error code [16916]; [FMWGEN][SQLServer JDBC Driver][SQLServer]A cursor with the name 'FetchRegion' does not exist.; nested exception is java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]A cursor with the name 'FetchRegion' does not exist

while accessing it from multi threaded java application. I've tried adding LOCAL after CURSOR declaration but it doesn't work.

DECLARE FetchRegion CURSOR READ_ONLY FOR
    SELECT ......

OPEN FetchRegion 

FETCH NEXT FROM FetchRegion INTO @RGN

WHILE @@ROWCOUNT <> 0
BEGIN
    .....
    FETCH NEXT FROM FetchRegion INTO @RGN
END 

CLOSE FetchRegion
DEALLOCATE FetchRegion

Yes it is used in a stored procedure which is being called from a java application. — Java Team
– Java Team, Commented Feb 25, 2018 at 14:41
Actually i'm using local DECLARE FetchYear CURSOR READ_ONLY FOR but its still giving doesn't exist error — Java Team
– Java Team, Commented Feb 25, 2018 at 14:46
Could you try with: DECLARE FetchRegion CURSOR LOCAL FAST_FORWARD FORWARD_ONLY FOR — McNets
– McNets, Commented Feb 25, 2018 at 14:59

Jorge Ribeiro · Accepted Answer · 2018-02-26 16:05:44Z

You can use this sample, i used a system table to get the first 10 rows, but you can create your own cursor.

DECLARE @cursor_name AS NVARCHAR(100)

SET @cursor_name = 'sampleCursor' 
                   + Replace(Cast(Newid() AS VARCHAR(36)), '-', '') 

DECLARE @cursor_sql AS NVARCHAR(max) 

SET @cursor_sql = N'   DECLARE @name nvarchar(10)  DECLARE ' + @cursor_name + N' CURSOR FOR   select top 10 name from sys.all_columns OPEN ' + @cursor_name 
                  + N' FETCH NEXT FROM ' + @cursor_name 
                  + N' INTO @name WHILE @@FETCH_STATUS <> -1 BEGIN print @name FETCH NEXT FROM  ' + @cursor_name + N' INTO @name end CLOSE  ' 
                  + @cursor_name + N' DEALLOCATE  ' + @cursor_name 

PRINT @cursor_sql 

EXECUTE Sp_executesql 
  @cursor_sql

To the cursor name is added a guid, to guarantee that is always different.

Then a query is created and executed based on that name.

Hope it helps!

Drew · Accepted Answer · 2024-02-07 19:59:41Z

1

Not sure if anyone else will find this from a similar search, but we had a similar problem with duplicate cursor names and executing dynamic sql is a security nightmare. We resolved the issue by naming the cursor with a scope-temporary prefix (#) and it seemed to resolve the issue.

DECLARE #iter CURSOR FOR ...

OPEN #iter
FETCH NEXT FROM #iter INTO ...

WHILE @@FETCH_STATUS = 0
BEGIN
     ...
     FETCH NEXT FROM #iter INTO ...
END

CLOSE #iter
DEALLOCATE #iter

edited Feb 7, 2024 at 19:59

answered Feb 7, 2024 at 19:57

Drew

112 bronze badges

Comments

AMINCHAR · Accepted Answer · 2018-02-25 14:22:40Z

0

You need to run it inside a dynamic sql statement. As a starting point you can check this question: Using a cursor with dynamic SQL in a stored procedure

answered Feb 25, 2018 at 14:22

AMINCHAR

2351 silver badge11 bronze badges

Collectives™ on Stack Overflow

How to name a cursor dynamically in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related