Prev: Business Intelligence on Mobile devices
Next: Distinct Column Level Output
From: phdate on 11 Dec 2009 14:49
Over the last couple of days, I have tried lots of ways to generate
sequential numbers within a user defined function, including a stored
proc to compute the next number and a function to return it. (I need
this ability as part of a larger scientific package, and it needs to be
a function). I get errors similar to this:

"Only functions and extended stored procedures can be executed from
within a function."



Here is the latest non-working code I have after many changes. In
essence, each time I call num_gen() function, I would like a new number
to be returned, and which can be used from other functions:


-- create the table
create table num_gen_table (id int)
go
insert into num_gen_table values (1)
go


-- sproc to move to next number
create proc update_num_gen_table as
update num_gen_table set id=id + 1
go


-- function to return the next number
create function num_gen () returns int as
begin
declare @new_num int

exec update_num_gen_table
select @new_num = id from num_gen_table
return @new_num
end


From: Erland Sommarskog on 11 Dec 2009 17:42
phdate (drscrypt(a)gmail.com) writes:
> Over the last couple of days, I have tried lots of ways to generate
> sequential numbers within a user defined function, including a stored
> proc to compute the next number and a function to return it. (I need
> this ability as part of a larger scientific package, and it needs to be
> a function). I get errors similar to this:
>
> "Only functions and extended stored procedures can be executed from
> within a function."
>
> Here is the latest non-working code I have after many changes. In
> essence, each time I call num_gen() function, I would like a new number
> to be returned, and which can be used from other functions:

A user-defined function cannot change state, so you are on the wrong
track entirely.

Since you say "it needs to be a function", I am not going to show
you any code now. You first have to back and change that requirement.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: phdate on 11 Dec 2009 18:26
Erland Sommarskog wrote:
> A user-defined function cannot change state, so you are on the wrong
> track entirely.

The state changes are a by-product of my attempts to get it working in
lieu of alternatives involving cursors. Other solutions that avoid the
state change are most welcome.


> Since you say "it needs to be a function", I am not going to show
> you any code now. You first have to back and change that requirement.


Well, it is needed in several places, and I was looking for a way to
avoid copying code blocks all over the place and instead have something
that could be named. If you think there is a better alternative, please
do show some code. I can seek to relax the requirements on my end.








From: Erland Sommarskog on 12 Dec 2009 05:14
phdate (drscrypt(a)gmail.com) writes:
> The state changes are a by-product of my attempts to get it working in
> lieu of alternatives involving cursors. Other solutions that avoid the
> state change are most welcome.

Well, you need to update the number generator, so that is a state
change.

> Well, it is needed in several places, and I was looking for a way to
> avoid copying code blocks all over the place and instead have something
> that could be named. If you think there is a better alternative, please
> do show some code. I can seek to relax the requirements on my end.

Say that you need to insert a number of rows in a table, with each
row being assigned a unique, sequential, id, the typical construct is:

BEGIN TRANSACTION

SELECT @nextid = coalesce(MAX(id), 1) FROM tbl WITH (UPDLOCK)

INSERT tbl (id, ....
SELECT @nextid + row_number() OVER (ORDER BY ...), ...
FROM

COMMIT TRANSACTION

Whether that fits in the context you are working, I don't know.
Furthermore, this solution requires SQL 2005 or higher.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Plamen Ratchev on 12 Dec 2009 10:21
Here is another example:
http://www.sqlmag.com/Article/ArticleID/101339/sql_server_101339.html

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Business Intelligence on Mobile devices
Next: Distinct Column Level Output