can i set an update only if SP is successful?

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  May 2, 2013 at 10:05 am

  #272025

  Hi,

  I have a SP which updates a flag before the select statement for a report in SSRS

  If for some reason the job dies in the middle I don't want the update to happen

  Can I control it so it happens at the end

  Thanks

• Jared

  SSC Guru

  Points: 61793

  More actions

  May 2, 2013 at 10:26 am

  #1611960

  We'd need more specific info, but if the select is contained in the same sp as the update, you can wrap them both in a transaction and rollback.

  Jared
  CE - Microsoft

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  May 2, 2013 at 10:33 am

  #1611967

  Hi Jared

  Thanks for getting back..

  I've tried this...

  There are two update that happen,

  one changes the flag for the previous run to completed

  the other changes null to Processed

  then selects ..

  If for some strange reason it dies I don't want any of the updates to happen...

  I tried below by misspelling a table name but the update still happened..

  Thanks....

  BEGIN TRAN T1

  begin try

  UPDATE dbo.USER_DEFINED_DATA

  SET CAD100 = 'Completed' --y

  --,CAD961 = getdate()

  WHERE (dbo.USER_DEFINED_DATA.EffDate = CONVERT(DATETIME, '2012-04-03 00:00:00', 102))

  AND ASSESSMENT_MONIKER = '00437C0817DB41FB9516F449084ACBF5'

  AND CAD100 = 'Processed' --x

  -- and OID = '9109E79868F14904813118C9F032FE64'

  --**

  -- This update sets records ready for export to report(Null) to "Processed"(X)

  --**

  UPDATE dbo.USER_DEFINED_DATA

  SET CAD100 = 'Processed' -- x

  , CAD961 = getdate()

  WHERE (dbo.USER_DEFINED_DATA.EffDate = CONVERT(DATETIME, '2012-04-03 00:00:00', 102))

  AND ASSESSMENT_MONIKER = '00437C0817DB41FB9516F449084ACBF5'

  AND CAD100 IS NULL

  -- and CAD100 = 'y'

  SELECT dbo.ASSESSMENT.AbbrName

  , dbo.USER_DEFINED_DATA.OID

  , dbo.USER_DEFINED_DATA.Expdate

  , dbo.user_defined_data.EffDate

  , dbo.USER_DEFINED_DATA.CAD4

  , dbo.USER_DEFINED_DATA.CAD16

  , dbo.USER_DEFINED_DATA.CAD100

  , dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER

  , dbo.user_defined_data.cad961

  FROM dbo.USER_DEFINED_DATA

  INNER JOIN dbo.ASSESSMENT

  ON dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER = dbo.ASSESSMENT.OID

  WHERE (dbo.USER_DEFINED_DATA.EffDate = CONVERT(DATETIME, '2012-04-03 00:00:00', 102))

  AND CAD100 = 'Processed'

  AND ASSESSMENT_MONIKER = '00437C0817DB41FB9516F449084ACBF5'

  --for xml auto

  if @@trancount > 0 commit tran T1

  end try

  begin catch

  if @@trancount > 0 rollback tran t1

  end catch

• Jared

  SSC Guru

  Points: 61793

  More actions

  May 2, 2013 at 10:37 am

  #1611969

  Is your select dependent on the changes from the update statement?

  Jared
  CE - Microsoft

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  May 2, 2013 at 10:41 am

  #1611970

  yes the second one takes null and updates to processed then selects based on field="processed"

  I know its probably the best way to do it....

• Jared

  SSC Guru

  Points: 61793

  More actions

  May 2, 2013 at 10:49 am

  #1611975

  Changing a table name to make one that does not exist should prevent compiling, period. What errors are you expecting to see that would create this situation? Also, if you are dependent on the updates for the select and they are in the same transaction, are you going to be able to read that data? (Not a question for me; Its for you...)

  Jared
  CE - Microsoft

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  May 2, 2013 at 10:53 am

  #1611976

  Now that you actually typed it out, I'm probably over thinking it....

  I''m thinking in the millisecond between getting the data and emailing the report sqlserver goes down...lol

  and in reality if it did happen the records are marked processed and I just rerun it....