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

phdate (drscrypt (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Erland Sommarskog wrote:
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, 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.

phdate (drscrypt (AT) gmail (DOT) com) writes:
Well, you need to update the number generator, so that is a state
change.

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 (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Here is another example:
http://www.sqlmag.com/Article/Articl...er_101339.html

--
Plamen Ratchev
http://www.SQLStudio.com

Erland Sommarskog wrote:


Unfortunately not. I use this technique elsewhere but what I need is
the ability to generate such numbers one at a time. And sometimes, I
need 2 or 3 of these at a time, depending on the results of the formulas
I use in the package.

Plamen Ratchev wrote:

Thanks but beyond the details of the author's specific case, I think it
is identical to the one I posted.

phdate (drscrypt (AT) gmail (DOT) com) writes:
Instead of row_number, you can use a correlated subquery with COUNT(*):

SELECT O.OrderID, O.CustomerID, O.OrderDate,
(SELECT COUNT(*)
FROM Orders O2
WHERE O2.CustomerID = O.CustomerID
AND O2.OrderID <= O.OrderID) AS OrdnoForCustomer
FROM Orders O
ORDER BY O.CustomerID, O.OrderID

The performance for larger result sets is awful, but for 2-3 it should
not be a problem.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx