Link to home
Start Free TrialLog in
Avatar of AlexPonnath
AlexPonnathFlag for United States of America

asked on

How can i select a value and update in single stored procedure

i have a table which holds counter ID's and i would like to use a single stored procedure to return the current value and
increase it also by one..
How can i do this ...


CREATE PROCEDURE idlookup_SEL
(
      @name                                     char(10))
AS
BEGIN
    SELECT
       counter
      FROM dbo.idlookup t
     WHERE
       t.name = @name

     
     UPDATE dbo.idlookup
       SET
             counter   = @counter + 1
     WHERE
       name = @name
END
ASKER CERTIFIED SOLUTION
Avatar of Colosseo
Colosseo
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Colosseo
Colosseo
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AlexPonnath
AlexPonnath
Flag of United States of America image

ASKER

That gets me closer but i also want to return the original counter value to the user..
right now it gets the value, updates it but never shows what the value is, i hope i can do this with out
a 2nd query
Avatar of AlexPonnath
AlexPonnath
Flag of United States of America image

ASKER

Thanks,

with your last reply i was able to figure it out...

CREATE PROCEDURE idlookup_SEL
(
      @name                                     char(10))
AS
 
BEGIN
    SELECT
    counter
      FROM dbo.idlookup t
     WHERE
      t.name = @name

     
     UPDATE dbo.idlookup
       SET
             counter   = counter + 1
     WHERE
       name = @name
END
Avatar of Colosseo
Colosseo
Flag of United Kingdom of Great Britain and Northern Ireland image
so you want to return both the current and new counter value?

this will do that.

CREATE PROCEDURE idlookup_SEL
(
     @name                                    char(10))
AS

DECLARE @counter as int

BEGIN
    SELECT
     @counter = counter
      FROM dbo.idlookup t
     WHERE
      t.name = @name

  SET @counter = @counter + 1
     
     UPDATE dbo.idlookup
       SET
             counter   = @counter
     WHERE
       name = @name

    SELECT counter as origCount, @counter as newCount
      FROM dbo.idlookup t
     WHERE
      t.name = @name
END
Avatar of Colosseo
Colosseo
Flag of United Kingdom of Great Britain and Northern Ireland image
ah right i see what you mean now :)

Scott