1

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
McNets
  • 10,352
  • 3
  • 32
  • 61
Java Team
  • 65
  • 1
  • 9
  • 1
    Is this cursor used within an stored procedure? – McNets Feb 25 '18 at 14:11
  • Yes it is used in a stored procedure which is being called from a java application. – Java Team Feb 25 '18 at 14:41
  • Actually i'm using local DECLARE FetchYear CURSOR READ_ONLY FOR but its still giving doesn't exist error – Java Team Feb 25 '18 at 14:46
  • Please find my structure above – Java Team Feb 25 '18 at 14:52
  • Could you try with: `DECLARE FetchRegion CURSOR LOCAL FAST_FORWARD FORWARD_ONLY FOR` – McNets Feb 25 '18 at 14:59
  • Please find 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. – Java Team Feb 25 '18 at 15:14
  • apologies but my comment about incorrect data was wrong..the error is still there after CURSOR LOCAL FAST_FORWARD FORWARD_ONLY ..please find it in my last comment – Java Team Feb 25 '18 at 15:16
  • I assume you're able to execute the stored procedure on SSMS without errors – McNets Feb 25 '18 at 15:17
  • No in this case i'm not updating the records inside cursor ..but using that data to update other tables..but no updates for cursor data – Java Team Feb 25 '18 at 15:17
  • yes in SSMS it's working fine..just when i'm calling it from multiple java threads then problem is occurring – Java Team Feb 25 '18 at 15:18
  • if i switch to set operation instead of cursor will it work in multi-threaded environment? – Java Team Feb 25 '18 at 16:21
  • I'm not sure, I think there is some undesired block. – McNets Feb 25 '18 at 16:22
  • You should avoid cursors where possible. Convert this to a set based operation. – Steve Ford Feb 25 '18 at 23:58

2 Answers2

1

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!

Jorge Ribeiro
  • 1,128
  • 7
  • 17
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

AMINCHAR
  • 225
  • 1
  • 11