t-sql 2012 using error handling logic

Question

t-sql 2012 using error handling logic

wendy elizabeth

SSCoach

Points: 16915
More actions
January 26, 2017 at 3:47 pm

#318332

In a t-sql 2012 database, I am planning to run the following sql unless you make suggestions on how to change the sql.
This is the first time that I have used the following:
1. rollback and commit, and
2. try and catch blocks looking for potential errors that can get generated.
BEGIN TRANSACTION;
BEGIN TRY
insert into O.dbo.Stu
select personID
      ,enrollmentID
      ,attributeID = 3374
      ,value
      ,date
      from O.dbo.Stu
where attributeID = 997
insert into O.dbo.Stu
select personID
      ,enrollmentID
      ,attributeID = 3373
      ,value
      ,date
      from O.dbo.Stu
where attributeID = 996
Insert into O.dbo.Stu
   Select c1.personID
          ,c1.enrollmentID
          ,attributeID=case when c2.value = 'N' then 3371 else 3370 end
          ,c1.value
        ,c1.date
from O.dbo.Stu C1
LEFT JOIN O.dbo.Stu c2
     on c2.personID=c1.personID
      and (c2.date = c1.date or cast(c2.date as date) = cast(c1.date as date))
   and C2.attributeID= 997
where C1.attributeID = 1452
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;
If @@TRANCOUNT > 0
     COMMIT TRANSACTION;
GO
There are basically 3 insert statements.
Thus would you change the t-sql that I listed above where you have a better method on how to handle the
situation?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

wendy elizabeth - Thursday, January 26, 2017 3:47 PM
In a t-sql 2012 database, I am planning to run the following sql unless you make suggestions on how to change the sql.
This is the first time that I have used the following:
1. rollback and commit, and
2. try and catch blocks looking for potential errors that can get generated.
BEGIN TRANSACTION;
BEGIN TRY
insert into O.dbo.Stu
select personID
      ,enrollmentID
      ,attributeID = 3374
      ,value
      ,date
      from O.dbo.Stu
where attributeID = 997
insert into O.dbo.Stu
select personID
      ,enrollmentID
      ,attributeID = 3373
      ,value
      ,date
      from O.dbo.Stu
where attributeID = 996
Insert into O.dbo.Stu
   Select c1.personID
          ,c1.enrollmentID
          ,attributeID=case when c2.value = 'N' then 3371 else 3370 end
          ,c1.value
        ,c1.date
from O.dbo.Stu C1
LEFT JOIN O.dbo.Stu c2
     on c2.personID=c1.personID
      and (c2.date = c1.date or cast(c2.date as date) = cast(c1.date as date))
   and C2.attributeID= 997
where C1.attributeID = 1452
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;
If @@TRANCOUNT > 0
     COMMIT TRANSACTION;
GO
There are basically 3 insert statements.
Thus would you change the t-sql that I listed above where you have a better method on how to handle the
situation?

I would write it like this:

BEGIN TRY

BEGIN TRANSACTION;

INSERT INTO O.dbo.Stu

SELECT

personID

, enrollmentID

, attributeID = 3374

, [value] --RESERVED WORDS SHOULDN'T BE USE FOR OBJECT NAMES

, [date]

FROM O.dbo.Stu

WHERE attributeID = 997

UNION ALL

SELECT

personID

, enrollmentID

, attributeID = 3373

, [value]

, [date]

FROM O.dbo.Stu

WHERE attributeID = 996

UNION ALL

SELECT

c1.personID

, c1.enrollmentID

, attributeID=case when c2.value = 'N' then 3371 else 3370 end

, c1.[value]

, c1.[date]

from O.dbo.Stu C1

LEFT JOIN O.dbo.Stu c2

ON c2.personID=c1.personID

AND (c2.date = c1.date or cast(c2.date as date) = cast(c1.date as date))

AND C2.attributeID= 997

WHERE C1.attributeID = 1452

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

PRINT Error_Message();

ROLLBACK TRANSACTION;

END CATCH;

GO

drew.allen SSC Guru Points: 76996 More actions · Answer 2

Please use the SQL Code tags when posting SQL Code. SQL Code is much easier to read when proper indentation is used, and the SQL Code tags preserve that indentation. When you don't use the SQL Code tags, it treats it as normal text which collapse multiple spaces/tabs into one space.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

wendy elizabeth SSCoach Points: 16915 More actions · Answer 3

Joe Torre - Thursday, January 26, 2017 4:01 PM
wendy elizabeth - Thursday, January 26, 2017 3:47 PM
In a t-sql 2012 database, I am planning to run the following sql unless you make suggestions on how to change the sql.
This is the first time that I have used the following:
1. rollback and commit, and
2. try and catch blocks looking for potential errors that can get generated.
BEGIN TRANSACTION;
BEGIN TRY
insert into O.dbo.Stu
select personID
      ,enrollmentID
      ,attributeID = 3374
      ,value
      ,date
      from O.dbo.Stu
where attributeID = 997
insert into O.dbo.Stu
select personID
      ,enrollmentID
      ,attributeID = 3373
      ,value
      ,date
      from O.dbo.Stu
where attributeID = 996
Insert into O.dbo.Stu
   Select c1.personID
          ,c1.enrollmentID
          ,attributeID=case when c2.value = 'N' then 3371 else 3370 end
          ,c1.value
        ,c1.date
from O.dbo.Stu C1
LEFT JOIN O.dbo.Stu c2
     on c2.personID=c1.personID
      and (c2.date = c1.date or cast(c2.date as date) = cast(c1.date as date))
   and C2.attributeID= 997
where C1.attributeID = 1452
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;
If @@TRANCOUNT > 0
     COMMIT TRANSACTION;
GO
There are basically 3 insert statements.
Thus would you change the t-sql that I listed above where you have a better method on how to handle the
situation?
I would write it like this:
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO O.dbo.Stu
SELECT
personID
, enrollmentID
, attributeID = 3374
, [value] --RESERVED WORDS SHOULDN'T BE USE FOR OBJECT NAMES
, [date]
FROM O.dbo.Stu
WHERE attributeID = 997
UNION ALL
SELECT
personID
, enrollmentID
, attributeID = 3373
, [value]
, [date]
FROM O.dbo.Stu
WHERE attributeID = 996
UNION ALL
SELECT
c1.personID
, c1.enrollmentID
, attributeID=case when c2.value = 'N' then 3371 else 3370 end
, c1.[value]
, c1.[date]
from O.dbo.Stu C1
LEFT JOIN O.dbo.Stu c2
ON c2.personID=c1.personID
AND (c2.date = c1.date or cast(c2.date as date) = cast(c1.date as date))
AND C2.attributeID= 997
WHERE C1.attributeID = 1452
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT Error_Message();
ROLLBACK TRANSACTION;
END CATCH;
GO

I have the following questions about 'PRINT Error_Message();
1. Where is the error message displayed at? Is this in sql server log files? This sql will be executed in SSIS manager by the DBA in production. Will this message appear in SSIS manager in an output window?
2. For 'PRINT Error_Message(), would you show me an example of what you actually place in this message area?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

wendy elizabeth - Friday, January 27, 2017 8:15 AM
I have the following questions about 'PRINT Error_Message();
1. Where is the error message displayed at? Is this in sql server log files? This sql will be executed in SSIS manager by the DBA in production. Will this message appear in SSIS manager in an output window?
2. For 'PRINT Error_Message(), would you show me an example of what you actually place in this message area?

I'm not sure what you mean by "SSIS manager" or "message area". But I'd use THROW or RAISERROR instead of print. That way, you can do fancy error-hendling stuff such as using different levels and severities, and writing custom error messages. If the code is in an SSIS package then make sure you have logging enabled so that you catch important output such as this.

John

Joe Torre SSChampion Points: 10248 More actions · Answer 5

wendy elizabeth - Friday, January 27, 2017 8:15 AM
Joe Torre - Thursday, January 26, 2017 4:01 PM
wendy elizabeth - Thursday, January 26, 2017 3:47 PM
In a t-sql 2012 database, I am planning to run the following sql unless you make suggestions on how to change the sql.
This is the first time that I have used the following:
1. rollback and commit, and
2. try and catch blocks looking for potential errors that can get generated.
BEGIN TRANSACTION;
BEGIN TRY
insert into O.dbo.Stu
select personID
      ,enrollmentID
      ,attributeID = 3374
      ,value
      ,date
      from O.dbo.Stu
where attributeID = 997
insert into O.dbo.Stu
select personID
      ,enrollmentID
      ,attributeID = 3373
      ,value
      ,date
      from O.dbo.Stu
where attributeID = 996
Insert into O.dbo.Stu
   Select c1.personID
          ,c1.enrollmentID
          ,attributeID=case when c2.value = 'N' then 3371 else 3370 end
          ,c1.value
        ,c1.date
from O.dbo.Stu C1
LEFT JOIN O.dbo.Stu c2
     on c2.personID=c1.personID
      and (c2.date = c1.date or cast(c2.date as date) = cast(c1.date as date))
   and C2.attributeID= 997
where C1.attributeID = 1452
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;
If @@TRANCOUNT > 0
     COMMIT TRANSACTION;
GO
There are basically 3 insert statements.
Thus would you change the t-sql that I listed above where you have a better method on how to handle the
situation?
I would write it like this:
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO O.dbo.Stu
SELECT
personID
, enrollmentID
, attributeID = 3374
, [value] --RESERVED WORDS SHOULDN'T BE USE FOR OBJECT NAMES
, [date]
FROM O.dbo.Stu
WHERE attributeID = 997
UNION ALL
SELECT
personID
, enrollmentID
, attributeID = 3373
, [value]
, [date]
FROM O.dbo.Stu
WHERE attributeID = 996
UNION ALL
SELECT
c1.personID
, c1.enrollmentID
, attributeID=case when c2.value = 'N' then 3371 else 3370 end
, c1.[value]
, c1.[date]
from O.dbo.Stu C1
LEFT JOIN O.dbo.Stu c2
ON c2.personID=c1.personID
AND (c2.date = c1.date or cast(c2.date as date) = cast(c1.date as date))
AND C2.attributeID= 997
WHERE C1.attributeID = 1452
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
PRINT Error_Message();
ROLLBACK TRANSACTION;
END CATCH;
GO
I have the following questions about 'PRINT Error_Message();
1. Where is the error message displayed at? Is this in sql server log files? This sql will be executed in SSIS manager by the DBA in production. Will this message appear in SSIS manager in an output window?
2. For 'PRINT Error_Message(), would you show me an example of what you actually place in this message area?

In production I would write to a log table typically, to help resolve issues, in addition one could email someone using dbmail.