Sql-server – Building test site, update dates (back date records)

datetimesql-server-2008update

I have 3 tables that all connect and I need to back date them in an MS SQL 2008 setting.
I was thinking about how to do this and not sure if I am on the correct path. The big problem I have is I have columns that are null, and I want to keep them null if they are. Otherwise I want to adjust the date by 1,2,3 days depending on value….
Here is what I am trying, it adjusts the 2nd and 3rd tables, but the first I assume is failing and I am not understanding why.

    BEGIN TRANSACTION
declare @NumberOfDays int = -1
declare @Subject varchar(70) = 'Daily Nurse Rounding -1'
declare @newSubject varchar(70) = 'Daily Nurse Rounding'
UPDATE hdIssues
SET hdIssues.IssueDate = IsNull(hdIssues.IssueDate,DATEADD(day,@NumberOfDays,hdIssues.IssueDate)),
hdIssues.StartDate = IsNull(hdIssues.StartDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.ResolvedDate = IsNull(hdIssues.ResolvedDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.LastUpdated = IsNull(hdIssues.LastUpdated, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.DueDate = IsNull(hdIssues.DueDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate )),
hdIssues.AcknowledgeDate = IsNull(hdIssues.AcknowledgeDate, DATEADD(day,@NumberOfDays,hdIssues.StartDate ))
Where
hdIssues.Subject = @Subject

UPDATE hdComments 
SET hdComments.CommentDate = DATEADD(day,@NumberOfDays,hdComments.CommentDate)
WHERE hdComments.IssueID in(select hdIssues.IssueID from hdIssues where hdIssues.Subject = @Subject)

UPDATE hdStatusChangeHistory 
SET hdStatusChangeHistory.ModifiedDateTime = DATEADD(day,@NumberOfDays,hdStatusChangeHistory.ModifiedDateTime)
WHERE hdStatusChangeHistory.IssueID in(select hdIssues.IssueID from hdIssues where hdIssues.Subject = @Subject)

UPDATE hdIssues
SET hdIssues.Subject = @newSubject
where hdIssues.Subject = @Subject

Best Answer

I think I got it.....

SET hdIssues.IssueDate =  COALESCE(DATEADD(day,@NumberOfDays,hdIssues.IssueDate),null),
hdIssues.StartDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.StartDate ),null),
hdIssues.ResolvedDate =  COALESCE(DATEADD(day,@NumberOfDays,hdIssues.ResolvedDate ),null),
hdIssues.LastUpdated =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.LastUpdated ),null),
hdIssues.DueDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.DueDate ),null),
hdIssues.AcknowledgeDate =  COALESCE( DATEADD(day,@NumberOfDays,hdIssues.AcknowledgeDate ),null)

Coalesce seemed to work for me, I had no idea what coalesce was before but saw it in this example and read up on it here

Anyone know of any drawbacks to this approach?

Related Solutions

Sql-server – Inserting and updating and selecting at thousands of times per second

Change the updates to use WITH (ROWLOCK). That will reduce the locks from page level to row level. You can try the same on the inserts as well.

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Sql-server – Inserting and updating and selecting at thousands of times per second

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Related Question