Using ANSI SQL style DECLARE CURSOR statement in UDF...

  • Article

Today I encountered a post in the Microsoft Technical forums where a user was trying to use the ANSI SQL style DECLARE CURSOR statement which will raise error # 1049 in SQL Server. This behavior is true in SQL Server 2000. Here is a code sample that demonstrates the problem:

 

create function cur_test (@id int)
returns int
as
begin
declare cur_test scroll cursor for select o.id from sysobjects as o;
return 0;
end

 

This will produce error message:

 

Msg 1049, Level 15, State 1, Procedure cur_test, Line 5
Mixing old and new syntax to specify cursor options is not allowed.

 

Now, let us look at the reason for the error message. In TSQL, user-defined functions cannot modify state of the database or persist information between executions. This is one of the reasons to disallow insert/update/delete on permanent tables or dynamic SQL. This also means that UDFs will disallow cursors that are global in nature which happens to be the case with those declared using the ANSI SQL syntax. Note that there is no way to specify cursor as local using the ANSI SQL syntax. So we default all cursors declared within UDFs to be local implicitly. This creates a conflict in case of the DECLARE CURSOR statement using ANSI SQL syntax since that doesn't support the local option. Hence the error message about mixing old and new syntax for cursor options. The workaround is to use TSQL syntax for the DECLARE CURSOR statement to specify LOCAL or leave it out in which case we default to LOCAL anyway. Here is the modified code:

 

create function cur_test (@id int)
returns int
as
begin
declare cur_test cursor scroll for select o.id from sysobjects as o;
return 0;
end

 

-- or

create function cur_test (@id int)
returns int
as
begin
declare cur_test cursor local scroll for select o.id from sysobjects as o;
return 0;
end

 

--

Umachandar Jayachandran

Comments

  • Anonymous
    August 16, 2006
    Thank You!! Very usefull!!!!! Great explication also.

  • Anonymous
    October 18, 2006
    I have that problem but if i try to change the sytax as you say then i get another error: Incorrect syntax near the keyword 'CURSOR'. Can you help me?

  • Anonymous
    October 18, 2006
    The syntax I suggested will work fine. Can you please post sample code that raises the error? -- Umachandar

  • Anonymous
    June 15, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=22862

  • Anonymous
    February 04, 2010
    Thanks a lot. Saved me plenty of headache and frustration. With your explanation it was solved in 1 minute. By the way: I encountered this behavior in SQL Server 2005.