Using SCROLL CURSOR in a user defined function

Question

Post reply

Using SCROLL CURSOR in a user defined function

Christian Gray

SSC Enthusiast

Points: 116
More actions
June 21, 2005 at 4:01 pm

#362980

I'm trying to use a scroll cursor inside a user defined function as follows:
-----------------------------------------------------------------------------
CREATE FUNCTION udf_manufacturerName (@partNum varchar(50), @num INT)
RETURNS varchar(50) AS
BEGIN
DECLARE @manname varchar(50)
DECLARE names_cursor SCROLL CURSOR FOR
   SELECT Manufacturer
   FROM    PartNumManufacturerNum
   WHERE [Part Number] = @partNum
   ORDER BY [Part Number], [Manufacturer Part Number], [Manufacturer]
OPEN names_cursor
FETCH ABSOLUTE @num FROM names_cursor INTO @manname
CLOSE names_cursor
DEALLOCATE names_cursor
RETURN @manname
END
-------------------------------------------------------------------------------
When I check the syntax it gives the following error:
"Error 1049: Mixing old and new syntax to specify cursor options is not allowed."
I've narrowed it down to the SCROLL keyword, when I take it out the syntax check passes but of course the function fails because you can't use ABSOLUTE with SCROLL. I've found away around it below:
-------------------------------------------------------------------------------
CREATE FUNCTION udf_manufacturerName (@partNum varchar(50), @num INT)
RETURNS varchar(50) AS
BEGIN
DECLARE @manname varchar(50)
DECLARE @currentNum INT
SET @currentNum = 0
DECLARE names_cursor CURSOR
FOR SELECT Manufacturer
FROM PartNumManufacturerNum
WHERE [Part Number] = @partNum
ORDER BY [Part Number], [Manufacturer Part Number], [Manufacturer]
OPEN names_cursor
WHILE @currentNum < @num
BEGIN
SET @manname = NULL
FETCH NEXT FROM names_cursor INTO @manname
SET @currentNum = @currentNum + 1
END
CLOSE names_cursor
DEALLOCATE names_cursor
RETURN @manname
END
-------------------------------------------------------------------------------
This works but it seems like the first method would be better since I can get the row I want directly without having to loop.
Does anyone have any ideas why the first example doesn't work, or what the correct syntax for a scroll cursor within a user defined function is?
Thanks,
Christian

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ND-215452 SSCrazy Points: 2037 More actions · Answer 1

ND-215452

SSCrazy

Points: 2037

June 21, 2005 at 4:07 pm

#567280

deallocate your cursor before exit function.

Christian Gray SSC Enthusiast Points: 116 More actions · Answer 2

Sorry for the typo. I updated it but that wasn't the problem.

Thanks for the reply though.

Ananda Kumar SSCommitted Points: 1865 More actions · Answer 3

CREATE FUNCTION udf_manufacturerName (@partNum varchar(50), @num INT)

RETURNS varchar(50) AS

BEGIN

DECLARE @manname varchar(50)

DECLARE names_cursor CURSOR static FOR

SELECT Manufacturer

FROM PartNumManufacturerNum

WHERE [Part Number] = @partNum

ORDER BY [Part Number], [Manufacturer Part Number], [Manufacturer]

OPEN names_cursor

FETCH ABSOLUTE @num FROM names_cursor INTO @manname

CLOSE names_cursor

DEALLOCATE names_cursor

RETURN @manname

END

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Have you tried the equivalent set based statement to see which on is the fasted?

Christian Gray SSC Enthusiast Points: 116 More actions · Answer 5

Thanks ~Ananda, that worked perfectly.

Remi, I've never used a set based statement (that I know of anyway) can you give an example

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Simple one :

Declare @Rank as int

set @Rank = 81

Select O1.Name, count(*) as Rank from dbo.SysObjects O1 inner join dbo.SysObjects O2 ON O1.Name > O2.Name group by O1.Name having count(*) = @Rank