April 7, 2008 at 12:06 am
Hi all,
Is it possible to create a cursor inside a UDF..If possible can anyone give an example..
Thanks in Advance
April 7, 2008 at 2:12 am
Ratheesh.K.Nair (4/7/2008)
Hi all,Is it possible to create a cursor inside a UDF..If possible can anyone give an example..
Thanks in Advance
Yes, it is. (but try to avoid this). An example (just a sum, and using a cursor here is a @@@@ :))
CREATE TABLE foo ( a INT )
go
INSERT INTO foo VALUES ( 1 )
go
CREATE FUNCTION f1 ( )
RETURNS INT
AS BEGIN
DECLARE @result INT
DECLARE @currentNumber INT
SET @result = 0
DECLARE cursor1 CURSOR
FOR SELECT a
FROM foo
OPEN cursor1
FETCH NEXT FROM cursor1 INTO @currentNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @result = @result + @currentNumber
FETCH NEXT FROM cursor1 INTO @currentNumber
END
CLOSE cursor1
DEALLOCATE cursor1
RETURN @result
END
GO
SELECT dbo.f1()
Regards,
Andras
April 7, 2008 at 4:17 am
In UDF you have even less reasons to use cursor as you can't call store procedure. Most of times it can be rewritten to use custom aggregate:
DECLARE @StaffList varchar(1024)
SELECT @StaffList = COALESCE(@StaffList + ',', '') + rtrim(employee_name)
FROM dbo.employees order by employee_name asc
return @StaffList
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply