Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Get last updated records?

    If I update a recordset a group of records using dynamic SQL where I update the TOP n records, is it possible to get the set of records that was updated?

    Code:
    CREATE PROCEDURE usp_Structural_ScheduleComponent
    @cProject char(7),
    @cComponentID char(10),
    @iPour int,
    @iQuantity int,
    @iAvailable int OUTPUT,
    @dtCast datetime OUTPUT
    AS
    
    SET @dtCast = convert(char(10), getdate(), 120)
    
    DECLARE @cSql varchar(500)
    SET @cSql = 'UPDATE tbStructuralComponentSchedule SET PourNumber = ' + CAST (@iPour AS VARCHAR) + ', ScheduledDate = ' + '''' + CAST(@dtCast AS VARCHAR) + '''' +
    		' WHERE EntryID IN ( SELECT TOP ' + CAST(@iQuantity AS VARCHAR) + 
    		' FROM tbStructuralComponentSchedule ' +
    		' WHERE fkProjectNumber = ' + '''' + @cProject + '''' +
    		' AND fkComponentID = ' + '''' + @cComponentID + '''' + 
    		' AND IssueDate IS NOT NULL' +
    		' AND ScheduledDate IS NULL' +
    		' ORDER BY EntryID DESC)'
    
    EXEC(@cSql)
    IF(@@ERROR <> 0 OR @@ROWCOUNT < = 0)
    	RAISERROR('Failed to add components to pour!',16,1)
    
    SELECT @iAvailable = SUM(CASE WHEN IssueDate IS NOT NULL AND ScheduledDate IS NULL THEN 1 ELSE 0 END)
    FROM tbStructuralComponentSchedule WHERE fkProjectNumber = @cProject AND fkComponentID = @cComponentID
    
    GO
    -- Is there a way to return the recordset that were modified in the update?

    Mike B

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your should run your query first as a SELECT statement and store the records in a temporary table or table variable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by blindman
    Your should run your query first as a SELECT statement and store the records in a temporary table or table variable.
    Makes sence, this actually occured to me a little after I had posted.

    Thanks for the reply!

    Mike B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •