Huh? What is TQL? A new query language...Tequila Query Language perhaps?
Nope. A SQL Server Template. When I'm not stealing code, I'll use a template to get sprocs going. For the most part, Error Handling in a sproc is the same (well for me) 99% of the time.
Just cut and paste the code in to a *.tql file in the folder:
C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\Create Procedure
Then in QA Do [CTRL]+[SHIFT]+{INSERT] to insert a new template. To update the values in the template (for example the new name of your sproc, [CTRL]+[SHIFT]+M. A dialog will appear and you can change the default values to whatever you want.
UPDATE: 10/11/2004 - OK, well that took awhile. 1st, I added the OUTPUT Variable to check for a return code, instead of using RETURN @rc, which can be over-ridden by SQL Server. I'll have to post the sample code that shows this. You need to let SQL Server manage the return value itself. Besides, it's no big deal to add the variable. 2nd, it seems that there is no longer any need to alter the code. You can just cut and paste this code as is. If you replace all the variables with the default values, it should just run and provide you a working model sample.
Good Luck!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[< stored_procedure_name, sysname, usp_New >]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[< stored_procedure_name, sysname, usp_New >]
GO
CREATE PROCEDURE [dbo].[< stored_procedure_name, sysname, usp_New >] @rc int OUTPUT
AS
--
-- Enterprise Solutions
--
-- File: < location_of_script, varchar(255), C:\TEMP\ >
-- Date: < Creation_Date, Date, GetDate() >
-- Author: < author_name, varchar(255), Brett Kaiser >
-- Server: < server_name, varchar(255), NJROS1D151\NJROS1D151DEV >
-- Database: < db_Name, varchar(255), TaxReconDB >
-- Login: < Login, varchar(255), sa >
-- Description: < short_desc, varchar(255), This Procedure will >
--
--
-- The stream will do the following:
--
-- < Functions, varchar(255), '1. Function... >
--
-- Tables Used: < Tables_Used, varchar(255), Table >
--
-- Tables Created: < Tables_Created, varchar(255), Table >
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- -------------------- ----------- ------------------ ------------------ ------------------ ------------------
--
--
-- sp_spaceused tblAcct_LedgerBalance
--Change Log
--
-- UserId Date Description
-- ----------- -------------- ------------------------------------------------------------------------------------------
-- < Author_Id, Varchar(8), x002548 > < Install_Date, DateTime, GetDate > < Init_Rel, varchar(255), 1. Initial release >
--
--
--
Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int
BEGIN TRAN
SET @rc = 0
< SQL_Statement, varchar(255), SELECT * FROM Northwind.dbo.Orders >
Select @Result_Count = @@ROWCOUNT, @error_out = @@error
If @error_out < > 0
BEGIN
SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
GOTO < stored_procedure_name, sysname, usp_New >_Error
END
If @Result_Count = 0 -- Business Logic Error...This one for an expected row but no results
BEGIN
SELECT @Error_Loc = 1
, @Error_Message = 'Put appropriate message here'
, @Error_Type = 50002, @rc = -2
GOTO < stored_procedure_name, sysname, usp_New >_Error
END
COMMIT TRAN
< stored_procedure_name, sysname, usp_New >_Exit:
-- Place any house keeping procedures here like...
Set ansi_warnings ON
RETURN
< stored_procedure_name, sysname, usp_New >_Error:
Rollback TRAN
If @Error_Type = 50001
BEGIN
Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
+ ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @error_out)
END
If @Error_Type = 50002
BEGIN
Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
END
RAISERROR @Error_Type @Error_Message
GOTO < stored_procedure_name, sysname, usp_New >_Exit
GO
DECLARE @rc int
EXEC [dbo].[< stored_procedure_name, sysname, usp_New >] @rc OUTPUT
SELECT 'Return Code: ' + CONVERT(varchar(15),@rc)
GO
www.tequila.com