1

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

Community
  • 1
  • 1
VoodooChild
  • 9,776
  • 8
  • 66
  • 99
  • that seems like voodoo programming. afaik, this is the first time i've seen that kind of query(updating while doing select) question here in SO – Hao Jul 01 '10 at 00:18
  • Based on the accepted answer from the other post it looks like you're missing a commit statement. Does that take place automatically in Sybase? – g.d.d.c Jul 01 '10 at 03:16
  • I don't have access to Sybase, so I can't run that code. However in SQL Server you can't make a function like that, you'd get the error: `Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.` I'm not sure how you'd do that in Sybase. – KM. Jul 01 '10 at 12:52
  • I tried it with MySQL, and got NULL in the second column. – Brian Hooper Jul 01 '10 at 15:10
  • @g.d.d.c: the results are same with or without the commit statement. – VoodooChild Jul 01 '10 at 15:38
  • What do you get if you run several individual select statements that just select your `read_and_increment()` proc? Does it behave as expected when used without another table involved? – g.d.d.c Jul 01 '10 at 16:03

1 Answers1

2

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
Calvin Allen
  • 4,176
  • 4
  • 31
  • 31