February 5, 2019 at 8:50 am
Hi Guys,
I need advice. I have almost 30 Update statements with different logic in one stored procedure. If I didn't use any Error catch
It will very hard for me to find out which Update statement has errored out. Could anyone Please advice, Use Begin Catch/End Catch and
raise/Print error in all 30 update statements to find out what is the error and where is the error. Is this the best way to handle this?
Thank You.
February 5, 2019 at 12:27 pm
One thing you can do to help, is in the RAISERROR command, there is a STATE parameter that you can set to mark which one of your updates is creating the error. It's just a number column, but if the same error could be generated multiple places it's often easier to find by STATE then trying to use DEBUG tools in T-SQL
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017
February 5, 2019 at 6:08 pm
rocky_498 - Tuesday, February 5, 2019 8:50 AMHi Guys,I need advice. I have almost 30 Update statements with different logic in one stored procedure. If I didn't use any Error catch
It will very hard for me to find out which Update statement has errored out. Could anyone Please advice, Use Begin Catch/End Catch and
raise/Print error in all 30 update statements to find out what is the error and where is the error. Is this the best way to handle this?Thank You.
If you "didn't use any Error catch", why do you think it would be difficult for you to determine which line or snippet of code causes an error? SQL server returns the error line number as a part of the error that it naturally raises.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2019 at 2:19 am
To expand on Jeff's comment, the following shows information that is availableBEGIN TRY
-- Generate a divide-by-zero error.
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;
On occasion I have, in addition, used code such as this to give a more meaningful message (e.g. for writing into some error log), which could well be useful where you have a whole bunch of stages as you describeDeclare @What varchar(50);
BEGIN TRY
set @What = 'Set thing';
update Thing set Thing = Bling + Fling;
set @What = 'Increment thing';
update Thing set Thing = Thing+1;
...
END TRY
BEGIN CATCH
SELECT @What ...;
END CATCH;
February 6, 2019 at 5:36 am
rocky_498 - Tuesday, February 5, 2019 8:50 AMHi Guys,I need advice. I have almost 30 Update statements with different logic in one stored procedure. If I didn't use any Error catch
It will very hard for me to find out which Update statement has errored out. Could anyone Please advice, Use Begin Catch/End Catch and
raise/Print error in all 30 update statements to find out what is the error and where is the error. Is this the best way to handle this?Thank You.
Total side note. You know that when that procedure with 30 individual statements gets compiled, all the statements gets compiled at once, regardless of the path the code travels down. So, some of those 30 statements may be getting compiled with parameter values that are not going to result in the best possible execution plans. You might want to consider making this a wrapper procedure and put all the individual statements into individual procedures to ensure that each compiles individually.
As to the initial question, the others have it covered.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2019 at 12:18 pm
Grant Fritchey - Wednesday, February 6, 2019 5:36 AMTotal side note. You know that when that procedure with 30 individual statements gets compiled, all the statements gets compiled at once, regardless of the path the code travels down. So, some of those 30 statements may be getting compiled with parameter values that are not going to result in the best possible execution plans. You might want to consider making this a wrapper procedure and put all the individual statements into individual procedures to ensure that each compiles individually.As to the initial question, the others have it covered.
In addition to the above - what process requires 30 separate update statements to be performed at the same time? Are these update statements all updating the same table - with separate logic for each update? How are these updates related?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 6, 2019 at 12:39 pm
Jeff Moden - Tuesday, February 5, 2019 6:08 PMrocky_498 - Tuesday, February 5, 2019 8:50 AMHi Guys,I need advice. I have almost 30 Update statements with different logic in one stored procedure. If I didn't use any Error catch
It will very hard for me to find out which Update statement has errored out. Could anyone Please advice, Use Begin Catch/End Catch and
raise/Print error in all 30 update statements to find out what is the error and where is the error. Is this the best way to handle this?Thank You.
If you "didn't use any Error catch", why do you think it would be difficult for you to determine which line or snippet of code causes an error? SQL server returns the error line number as a part of the error that it naturally raises.
This is true as long as "error" means "SQL generated a failure preventing me from updating", and not "the update didn't do what I wanted it to do". If you're building in some form of validation that your updates "did what they are supposed to do" within your procedures, then TRY...CATCH become necessary along with RAISERROR.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 6, 2019 at 12:58 pm
Matt Miller (4) - Wednesday, February 6, 2019 12:39 PMJeff Moden - Tuesday, February 5, 2019 6:08 PMrocky_498 - Tuesday, February 5, 2019 8:50 AMHi Guys,I need advice. I have almost 30 Update statements with different logic in one stored procedure. If I didn't use any Error catch
It will very hard for me to find out which Update statement has errored out. Could anyone Please advice, Use Begin Catch/End Catch and
raise/Print error in all 30 update statements to find out what is the error and where is the error. Is this the best way to handle this?Thank You.
If you "didn't use any Error catch", why do you think it would be difficult for you to determine which line or snippet of code causes an error? SQL server returns the error line number as a part of the error that it naturally raises.
This is true as long as "error" means "SQL generated a failure preventing me from updating", and not "the update didn't do what I wanted it to do". If you're building in some form of validation that your updates "did what they are supposed to do" within your procedures, then TRY...CATCH become necessary along with RAISERROR.
Understood and absolutely agreed. The problem with most folks is that they have some sort of religion that stored procedures must have TRY/CATCH in them and then they end up throwing the error incorrectly in the TRY/CATCH making it much less useful than the normal "it failed" errors that T-SQL renders.
I'll try to remember to post what we use tonight after work.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2019 at 7:24 am
Jeff Moden - Wednesday, February 6, 2019 12:58 PMMatt Miller (4) - Wednesday, February 6, 2019 12:39 PMJeff Moden - Tuesday, February 5, 2019 6:08 PMrocky_498 - Tuesday, February 5, 2019 8:50 AMHi Guys,I need advice. I have almost 30 Update statements with different logic in one stored procedure. If I didn't use any Error catch
It will very hard for me to find out which Update statement has errored out. Could anyone Please advice, Use Begin Catch/End Catch and
raise/Print error in all 30 update statements to find out what is the error and where is the error. Is this the best way to handle this?Thank You.
If you "didn't use any Error catch", why do you think it would be difficult for you to determine which line or snippet of code causes an error? SQL server returns the error line number as a part of the error that it naturally raises.
This is true as long as "error" means "SQL generated a failure preventing me from updating", and not "the update didn't do what I wanted it to do". If you're building in some form of validation that your updates "did what they are supposed to do" within your procedures, then TRY...CATCH become necessary along with RAISERROR.
Understood and absolutely agreed. The problem with most folks is that they have some sort of religion that stored procedures must have TRY/CATCH in them and then they end up throwing the error incorrectly in the TRY/CATCH making it much less useful than the normal "it failed" errors that T-SQL renders.
I'll try to remember to post what we use tonight after work.
Definitely agreed on that. Right-size your error checking: if it's a system error - the cleanest thing you can do most of the time is to reflect back what the system told you (possibly after cleaning up any fallout from the failure if the system isn't going to do it for you).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply