Number generator
phdate
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
Erland Sommarskog
> 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:
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, esq...@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
phdate
> 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.
Erland Sommarskog
> 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.
Plamen Ratchev
http://www.sqlmag.com/Article/ArticleID/101339/sql_server_101339.html
--
Plamen Ratchev
http://www.SQLStudio.com
phdate
> 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:
> Whether that fits in the context you are working, I don't know.
> Furthermore, this solution requires SQL 2005 or higher.
>
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.
phdate
> Here is another example:
> http://www.sqlmag.com/Article/ArticleID/101339/sql_server_101339.html
>
Thanks but beyond the details of the author's specific case, I think it
is identical to the one I posted.
Erland Sommarskog
> 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.
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.