Sql Server stored procedure cursor name conflict

rjjhvcjd  于 2023-05-21  发布在  SQL Server
关注(0)|答案(6)|浏览(159)

I have a situation where one SQL Server stored procedure is calling another one and both happen to use cursors that have the same name. This is causing a conflict as apparently the cursors don't have visibility restricted to the stored procedure where they are declared.

Is there any way to make the cursors private to the procedure they were declared in?

vm0i2vca

vm0i2vca1#

Use the LOCAL option when declaring the cursor .
LOCAL

Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is only valid within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

3qpi33ja

3qpi33ja3#

Yes you can restrict the scope of a cursor to the stored procedure with

DECLARE CURSOR LOCAL

hc8w905p

hc8w905p4#

Are the cursors local cursors or global cursors?...Make them local or just change the name of one of the cursors and be done with it

hs1ihplo

hs1ihplo5#

Unless you specifically need a global cursor, use a local cursor.

If your cursor declaration does not include the keyword LOCAL or GLOBAL, then SQL Server will use its default server configuration. You can determine the default configuration by using this query:

SELECT is_local_cursor_default FROM sys.databases WHERE name = DB_NAME();
ttygqcqt

ttygqcqt6#

By default, if the LOCAL / GLOBAL switch isn't filled out when declaring the cursor, it will be implicitly set to LOCAL. I do however suggest that LOCAL be defined for other peoples clarity when reading your code.

Please refer to the following article note:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql

If you are writing two cursors within the same scope (same stored procedure), simply name them different names.

相关问题