SQL Server 2012/2008: handling errors from nested stored procedures


I have stored procedure usp_Main that calls multiple nested sprocs. I wrote 'try catch' in the usp_Main and all I want is to know which nested sproc generated error. Can I accomplish this without adding 'try catch' to every nested sproc?

Thank you in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
You can have try..catch within nested procedure, and try to return relevant error from nested procedure. Then you will know which error it is.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:

Note from the following
I'm logging the error procedure name, but also the name of the procedure where I caught the error. So initially I simply have to code one catch block for the top procedure.  
By using a standard variable name with the name of the procedure, the catch block can almost be entirely the same from procedure to procedure.
I also include a note string in case I want to tag the catch location inside a procedure.


Here are some code snippets:

create procedure dbo.YourProcedureNameHere
	@Results int output
	, @Debug int = 0
	set nocount on
	declare @ProcedureName sysname
	set @ProcedureName = 'dbo.YourProcedureNameHere'

	begin catch
		execute dbo.spGen_LogCatchSQLError
			@CallingProcedureName = @ProcedureName
			, @CallingLocationNote = '' 

		-- Dummy line for Template
		--print ''
	end catch

create procedure dbo.spGen_LogCatchSQLError
	@CallingProcedureName sysname
	, @CallingLocationNote sysname 
	set nocount on

	--Stored procedure code here
	declare @ProcedureName sysname
	set @ProcedureName = ''

	begin try
		-- Dummy line for Template
		print ''
		--Stored procedure code here
		insert dbo.GenericError( 
			, CallingProcedureName
			, CallingLocationNote
			, error_number
			, error_severity
			, error_state
			, error_procedure
			, error_line
			, error_message
				, @CallingProcedureName
				, @CallingLocationNote
				, error_number()
				, error_severity()
				, error_state()
				, error_procedure()
				, error_line()
				, error_message()
	end try
	begin catch
		execute ErrorTrapping.dbo.spGen_LogCatchSQLError
			@CallingProcedureName = @ProcedureName
			, @CallingLocationNote = '' 
	end catch

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.