UPDATE with WHERE clause frowned upon by OUTPUT?

• diamondgm

  SSCrazy

  Points: 2824

  More actions

  May 23, 2011 at 3:08 pm

  #236777

  Hi Folks,

  Wondering if you can help me...

  I was trying to help a friend with something and landed up writing the proc below for him as a demonstration.

  As demonstrations go, it was terrible.

  Could someone help me understand what I am doing wrong here?

  When proc will parse and successfully create if I comment out the WHERE clause in the UPDATE statement - but that's no good to me.

  Why will this not work with the WHERE clause?

  CREATE PROC CarProductionStateUpdate

  @CarID INT,

  @StateID INT

  AS

  BEGIN

  DECLARE @Inserted TABLE (CarID INT, BuildStateID INT, DateOfState DATETIME)

  BEGIN TRANSACTION

  BEGIN TRY

  UPDATE CarProduction

  SET BuildStateID = @StateID,

  DateStamp = GETDATE()

  --WHERE CarID = @CarID

  -- If the WHERE caluse is uncommented, the proc can not be created???

  OUTPUT DELETED.CarID, DELETED.BuildStateID, DELETED.DateStamp INTO @Inserted

  INSERT INTO [Sandbox].[dbo].[CarProductionHistory]

  ([CarID]

  ,[BuildStateID]

  ,[DateOfState])

  SELECT * FROM @Inserted

  COMMIT TRANSACTION

  END TRY

  BEGIN CATCH

  IF @@TRANCOUNT > 0

  ROLLBACK TRANSACTION;

  END CATCH

  END

  As this is probably a syntax (or PEBKAC) problem, I'm not posting the DDL for the tables unless it will help?

  Thanking you in advance.

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  May 23, 2011 at 3:14 pm

  #1328740

  Flip the ordering, output before where. It actually goes in front of the FROM but you don't have that there.

  See this for more details: http://msdn.microsoft.com/en-us/library/ms177564.aspx

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 23, 2011 at 3:15 pm

  #1328741

  piece of cake, it's only syntax.

  the WHERE statement goes AFTER the output...not before it:

  CREATE PROC CarProductionStateUpdate

  @CarID INT,

  @StateID INT

  AS

  BEGIN

  DECLARE @Inserted TABLE (CarID INT, BuildStateID INT, DateOfState DATETIME)

  BEGIN TRANSACTION

  BEGIN TRY

  UPDATE CarProduction

  SET BuildStateID = @StateID,

  DateStamp = GETDATE()

  OUTPUT DELETED.CarID, DELETED.BuildStateID, DELETED.DateStamp INTO @Inserted

  WHERE CarID = @CarID

  -- If the WHERE caluse is uncommented, the proc can not be created???

  INSERT INTO [Sandbox].[dbo].[CarProductionHistory]

  ([CarID]

  ,[BuildStateID]

  ,[DateOfState])

  SELECT * FROM @Inserted

  COMMIT TRANSACTION

  END TRY

  BEGIN CATCH

  IF @@TRANCOUNT > 0

  ROLLBACK TRANSACTION;

  END CATCH

  END

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• diamondgm

  SSCrazy

  Points: 2824

  More actions

  May 23, 2011 at 3:17 pm

  #1328743

  Doh! 😀

  Thanks a million guys!

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  May 23, 2011 at 4:34 pm

  #1328776

  Being SQL Server 2008, you can use Composable DML, which lets you wrap a statement generating an OUTPUT set with another DML operation:

  INSERT INTO [Sandbox].[dbo].[CarProductionHistory](CarID, BuildStateID, DateofState)

  SELECT CarID, BuildStateID, DateofState

  FROM (

  UPDATE CarProduction

  SET BuildStateID = @StateID,

  DateStamp = GETDATE()

  OUTPUT DELETED.CarID, DELETED.BuildStateID, DELETED.DateStamp

  WHERE CarID = @CarID

  ) AS T(CarID, BuildStateID, DateofState);

  Eddie Wuerch
  MCM: SQL

• diamondgm

  SSCrazy

  Points: 2824

  More actions

  May 24, 2011 at 1:29 am

  #1328857

  Very nice Eddie, thank you!