SQL query and stored procedure not producing desired results

Question

I have this table and Stored proc function:

Table:

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

Stored 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 I create this Numbers table as well

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:

when I do this:

select 
    f.Number
    ,read_and_increment()
from _Numbers f

I get :

  Number-----Value

   1          0   
   2          0   
   3          0   
   4          0   

I want different value like (0,1,2,3) - what do I need to do to achieve this?

I understand that I am getting the same values because of the single Select, but not sure what I need to do to get what I am after at the moment......

I cannot use IDENTITY or autoincrement see my previous question for more details if interested...

Thanks,

Voodoo

Answer 1

Try marking your function as NOT DETERMINISTIC and see if that helps. By default, all functions are deterministic, which means the database server can cache the result under certain circumstances. Marking it this way will force the server to re-evaluate the query/function each 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