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

Leave a Reply

Your email address will not be published. Required fields are marked *