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

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