CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user482375
user482375

Reputation:

Select and update in one stored procedure

I have a select stored procedure and I am trying to make it so the results it bring down it also updates a column called Downloaded and marks those rows as downloads.

For example, I pull down 10 rows those 10 rows I also want to update the Downloaded column to true all in the same stored procedure. Is this possible?

This is my sp so far, it pulls down the data.

ALTER PROCEDURE [dbo].[GetLeads]
@DateTo datetime = null,
@DateFrom datetime = null
AS
    SELECT name
         , lastname
         , title
         , company
         , address
         , address2
         , city
         , [state]
         , zip
         , country
         , stamptime

    FROM
        lead
    where
         ((@DateTo is null AND @DateFrom IS null) or (stamptime BETWEEN @DateTo AND @DateFrom))

Thanks!

Upvotes: 6

Views: 22214

Answers (3)

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Your best bet might be to use an OUTPUT statement with the UPDATE.

http://blog.sqlauthority.com/2007/10/01/sql-server-2005-output-clause-example-and-explanation-with-insert-update-delete/

DECLARE @TEMPTABLE
(
    name <type>
    , lastname <type>
    , title <type>
    , company <type>
    , address <type>
    , address2 <type>
    , city <type>
    , state <type>
    , zip <type>
    , country <type>
    , stamptime <type>
)

UPDATE a
SET a.Downloaded = 1
OUTPUT Inserted.name, Inserted.lastname, Inserted.title, etc. INTO @TEMPTABLE
FROM lead a
WHERE ((@DateTo IS NULL AND @DateFrom IS NULL) OR (a.stamptime BETWEEN @DateTo AND @DateFrom))

SELECT * FROM @TEMPTABLE

Upvotes: 2

Alex K.
Alex K.

Reputation: 175986

You can simply OUTPUT the updated rows;

UPDATE lead
  SET Downloaded = 1
OUTPUT INSERTED.* 
  WHERE ((@DateTo is null AND @DateFrom IS null) or (stamptime BETWEEN @DateTo AND @DateFrom))

This updates, then returns the updated rows in a single statement.

Upvotes: 13

dillenmeister
dillenmeister

Reputation: 1647

Continuing on vulkanino's comment answer, something like this:

ALTER PROCEDURE [dbo].[GetLeads]
@DateTo datetime = null,
@DateFrom datetime = null
AS
    UPDATE  
        lead 
    SET     
        Downloaded = 1 
    WHERE   
        ((@DateTo is null AND @DateFrom IS null) or (stamptime BETWEEN @DateTo AND @DateFrom))

    SELECT name
         , lastname
         , title
         , company
         , address
         , address2
         , city
         , [state]
         , zip
         , country
         , stamptime

    FROM
        lead
    where
         ((@DateTo is null AND @DateFrom IS null) or (stamptime BETWEEN @DateTo AND @DateFrom))

Upvotes: 4

Related Questions