SQL Server: My Quick and Dirty Way of Debugging Stored Procedure

I use the below SQL script to debug a stored procedure in chunks, replacing or adding into it the rest of the stored procedure code until every code checks out fine.  Since I use transactions, changes are temporary and it rolls back the transaction at the end.  I also used the error handling in SQL Server to catch and print the error line, error number and error message.

BEGIN TRANSACTION
BEGIN TRY
	--
	-- your SQL code here
	--
END TRY
BEGIN CATCH
	PRINT 'Error at line # ' + CAST(ERROR_LINE() AS VARCHAR(MAX)) 
		+ ': ' + CAST(ERROR_NUMBER() AS VARCHAR(MAX)) 
		+ ' - ' + ERROR_MESSAGE()
END CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s