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
AlexPonnathAsked:
Who is Participating?
 
ColosseoCommented:
Hi assuming counter is an int you can do this

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

DECLARE @counter as int

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

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

HTH

Scott
0
 
ColosseoCommented:
actually looking at it again if this is all the proc is doing you should be able to use

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

BEGIN
     UPDATE dbo.idlookup
       SET
             counter   = counter + 1
     WHERE
       name = @name
END
0
 
AlexPonnathAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AlexPonnathAuthor Commented:
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
0
 
ColosseoCommented:
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
0
 
ColosseoCommented:
ah right i see what you mean now :)

Scott
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.