[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-04-25
6
Medium Priority
?
212 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:AlexPonnath
  • 4
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
Colosseo earned 2000 total points
ID: 13857432
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
 
LVL 15

Assisted Solution

by:Colosseo
Colosseo earned 2000 total points
ID: 13857442
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
 

Author Comment

by:AlexPonnath
ID: 13857448
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:AlexPonnath
ID: 13857468
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
 
LVL 15

Expert Comment

by:Colosseo
ID: 13857469
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
 
LVL 15

Expert Comment

by:Colosseo
ID: 13857473
ah right i see what you mean now :)

Scott
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question