CODEWITHSUNDEEP
sql-servert-sqlerror-handling
Mirza Baig
Mirza Baig

Reputation: 1

Can you capture the bad t-sql in error message

If I run a sql statement such as the following:

SELECT 1/0;

Is there a way to capture the statement "SELECT 1/0;" in an error message? The following does not give me the SQL that failed:

BEGIN TRY SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO

Also, I want to see if I can avoid using try catch at every statement. I have a SP that is executing a lot of SQL statements between a TRY and a CATCH statement. I want to know which one of the SQL statements failed among the numerous SQL statements in the TRY ... CATCH block.

All I have found so far is giving the error message details but not the T-SQL that failed.

Upvotes: 0

Views: 231

Answers (3)

Amit Kumar
Amit Kumar

Reputation: 1

You can use line number for same but if you want to check which statement having problem then you have to define statement number also to check the number and set the same number in a variable like this.

Declare @StmNo as int
BEGIN TRY
    set @StmNo=1
    SELECT GETDATE();
    set @StmNo=2
    SELECT 1/0;
END TRY 
BEGIN CATCH 
        SELECT 
            @StmNo AS StatementNumber,
            ERROR_NUMBER() AS ErrorNumber, 
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, 
            ERROR_PROCEDURE() AS ErrorProcedure, 
            ERROR_LINE() AS ErrorLine, 
            ERROR_MESSAGE() AS ErrorMessage; 
        END CATCH; 
        GO

Upvotes: 0

Manish
Manish

Reputation: 1

If your stored procedure got bunch of statements, it's always good to have a log variable set up at the beginning of the stored procedure. You can adjust the value of this variable to make sure you would want to log steps or not. In your current situation, you can set it to 1 and start logging all/necessary steps. That will help you in finding out what got executed and where is the error occurring .

Ex: DECLARE @bLog BIT = 0 -- Default when you do not want to log

SET @bLog = 1 IF (@bLog = 1) BEGIN ----- Add log here, take back up of result executed from previous steps --etc. END

Upvotes: 0

HLGEM
HLGEM

Reputation: 96640

You can leverage the fact that table variables are not rolled back. After each stament insert a success line into a table variable for logging.

If the proc succeed, nothing needs to be returned from the table variable. If it hits the catch block though, you can rollback the transaction and either retrun the select from the table variable or better, insert that information into a log table. If your proc sets a lot of variables, I would also log those values in this table so you can see what the values were at the time the proc failed. By putting it into a logging table, you have a record for all the times the proc fails, so if it fails on Friday night and fails several times but not every time over the weekend, you have your data about what worked and what the variables were at the time of failure to use to figure out what is happening. This is especially useful if you use dynamic sql because you could log the sql statement produced as well.

Upvotes: 1

Related Questions