I've this table and Saved proc function:

Table:

CREATE TABLE _DMigNumbers(
    Number numeric(20,0) NOT NULL PRIMARY KEY
);
INSERT INTO _DMigNumbers VALUES(0)

Saved proc function:

CREATE FUNCTION read_and_increment()
RETURNS NUMERIC(20,0)
BEGIN
    DECLARE @number_just_read NUMERIC(20,0);

      SELECT number INTO @number_just_read
        FROM _DMigNumbers;

      UPDATE _DMigNumbers
         SET number = number + 1;
   RETURN @number_just_read;
End

and that i create this Amounts table too

CREATE TABLE _Numbers (
    Number int NOT NULL PRIMARY KEY
);
INSERT INTO _Numbers VALUES(1)
INSERT INTO _Numbers VALUES(2)
INSERT INTO _Numbers VALUES(3)
INSERT INTO _Numbers VALUES(4)

NOW:

after i do that:

select 
    f.Number
    ,read_and_increment()
from _Numbers f

I recieve :


  Number-----Value

   1

   2

   3

   4

I would like different value like (,1,2,3) - what should i do to do this?

I realize that i'm obtaining the same values due to the only Choose, although not sure what I have to do in order to get things i am after right now......

I am unable to use IDENTITY or autoincrement see my previous question for more details if interested...

Thanks,

Voodoo

Try marking your work as NOT DETERMINISTIC and find out in the event that helps. Automatically, all functions are deterministic, meaning the database server can cache the end result under certain conditions. Marking it by doing this will pressure the server to re-assess the query/function every time.

CREATE FUNCTION read_and_increment()
RETURNS NUMERIC(20,0)
NOT DETERMINISTIC
BEGIN
    DECLARE @number_just_read NUMERIC(20,0);

      SELECT number INTO @number_just_read
        FROM _DMigNumbers;

      UPDATE _DMigNumbers
         SET number = number + 1;
   RETURN @number_just_read;
End