x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

TQL?

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

 

 

Print | posted on Tuesday, May 25, 2004 10:19 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: TQL?

Dang, Brett! I was really looking forward to the Tequila Query Language. Although I usually pronounce it as ta-kill-ya.

Good info on templates, though.
5/26/2004 6:18 PM | AjarnMark
Gravatar

# re: TQL?

you can make the angle brackets show up in sample code by coding them as html entities

e.g. &lt;grin /&gt;
5/27/2004 2:31 PM | rudy
Gravatar

# re: TQL?

You finally got me out of the SQLTeam forum and into this blog.

I've tried the template, and it does work great, and seems less filling too. So, the primary benefit of TQL is the parameter substitution throughout the template?

I doubt I'd have gotten into TQL this year without this push.

Thanks,

Sam
6/1/2004 1:45 PM | SamC
Gravatar

# re: TQL?

>> So, the primary benefit of TQL is the parameter substitution

Yeah, but you can also use it for "standard" type things you create...like the update trigger we've been seeing so often these days....

This is one example...most of the other code though I try and generate from the catalog...

Which reminds me...I should post the trigger generator code...it'll build audit triggers for every table in a database...


6/1/2004 1:53 PM | Brett
Gravatar

# Sql Server Templates

6/10/2004 3:31 PM | Scott Elkin
Gravatar

# re: TQL?

Ugh. So they reinvented perl formats (which reinvented fortran formats) in the DB?

I need a shower.
7/23/2004 3:55 PM | Sniffy McNickles
Gravatar

# re: TQL?

Huh?

No I believe you're confusing the template.

It just lets you build standard code sets quickly.

They are not in the DB.

It is an extension of the Query Analyzer developer tool.

Is that what you meant.

Wish you had put a link.

7/23/2004 4:01 PM | Brett
Gravatar

# re: TQL?

The only thing I'm missing is the switch to the proper database. When updating a bunch of procedures on a database, I use a batchfile which osql's the sp's stored on file against the server. Even when just opening the sp in the Analyzer, making a small change and executing it, I don't have to worry about the db.
8/25/2004 6:56 AM | Kaiowas
Gravatar

# Sql Server Templates

I keep saying this, but I can&#39;t believe I have been programming in Sql Server for this many years,
7/20/2006 7:48 PM | Scott Elkin
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET