How do I retain a variable value in a function? -- sql server msde

Jim Young
8/17/2004 9:04:50 AM

UDFs in SQL Server don't work that way. You'll need to store the last value
in a table (not a temp table) and synchronize access to it to prevent issues
with concurrent updates.

Jim

[quoted text, click to view]

June Macleod
8/17/2004 4:35:23 PM

I wish to write a user defined function which will fill a field in my table
with the next sequential number whenever the value in another field changes.
The problem I have is how do I have the @lastquest and @x variables keep the
value that they had the last time the function was called? In MS Access I
would have declared them as 'static'. Is their an equivilent? Or do I need
to do something like store the last values in a temporary table and look
them up each time? Any help would be much appreciated.

CREATE FUNCTION dbo.fn_getnextnumber (@val int)

RETURNS int AS

BEGIN

declare @lastquest int

declare @x int

If @val <> @lastquest

begin

set @x = 0

end

set @x = @x + 1

set @lastquest = @val

RETURN @X

END

sql server msde : How do I retain a variable value in a function?