Use Query Analyzer Templates to Speed Code Creation

This article is a little different from most others in the series in that instead of offering a UDF you're going to see an easier way to create UDFs when using SQL Query Analyzer, or Query Analyzer for short.

Query Analyzer offers a template feature that acts as an aid in producing all types of SQL code. There are templates for stored procedures, triggers, and user-defined functions. There are also templates for using some system stored procedures such as the ones to manage extended properties.

To use a template do the following:

1. In Query Analyzer use the menu command File -> New (CTRL + N).
2. Instead of picking the "Blank Query Windows" template, go into one of the subdirectories, such as the "Create Function" subdirectory and pick a template, such as "Create Inline Function". You'll get a new Query Analyzer window with the template copied to the window.
3. Use the Query Analyzer menu command Edit -> Replace Template Parameters (CTRL + SHIFT + M). This brings up the Replace Template Parameters windows which allows you to fill in a list of values. When you press the "Replace All" button, the parameters are substituted into the query window text and your T-SQL script is at least partially written.

Here's the standard template for an inline UDF:

-- =============================================
-- Create inline function (IF)
-- =============================================
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'')
	DROP FUNCTION 
GO

CREATE FUNCTION  
	(<@param1, sysname, @p1> , 
	 <@param2, sysname, @p2> )
RETURNS TABLE 
AS
	RETURN SELECT   @p1 AS c1, 
			@p2 AS c2
GO

-- =============================================
-- Example to execute function
-- =============================================
SELECT * 
FROM . 
	(, 
	 )
GO

As you can see it presumes two parameters. Of course, you'll probably have less or more than two but it gets you started.

Templates are stored in TQL files. What Query Analyzer is doing when you start (step A above) is to copy the TQL file into the new window.

Here's my template for an Inline UDF:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo. (

    <@parm1, sysname, @p1>  -- 
  , <@parm2, sysname, @p2>  -- 
  , <@parm3, sysname, @p3>  -- 
)   RETURNS TABLE
    WITH SCHEMABINDING -- Or relevant comment.
/* 
* description goes here
*
* Related Functions:
* Attribution: Based on xxx by yyy found in zzzzzzzzzzzzz
* Maintenance Notes:
* Example:
select * FROM dbo.
   (, , )
* Test:
* Test Script: TEST_
* History:
* When          Who     Description
* ------------- ------- -----------------------------------------
*               Initial Coding
****************************************************************/
AS RETURN

SELECT 
    FROM
    WHERE
    GROUP BY
    HAVING
    ORDER BY

GO

GRANT SELECT ON [dbo].[] 
        TO [PUBLIC]
-- GRANT INSERT, UPDATE, DELETE ON 
--         [dbo].[]  TO [PUBLIC]
GO

I've made it different from the standard template to reflect the way that I think UDFs should be written. For example, most of the template is the header comment. That is I think UDFs should always be documented. I also format UDFs differently than Query Analyzer's template. I use what's called Separator First Formatting (SFF), which places separators at the front of a line instead of at the end. Of course, you already know that because you've been reading this newsletter for a while.

Copy the template into a text processor such as Notepad and save it as a TQL file. TQL files are stored in the SQL Server code directory tree in the directory \Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer. I suggest that you put the template above into the "Create Function" directory under the name "TSQL UDFs Create Inline Function.TQL".  The text file is available for download here.

This is the last issue of Volume 2 of the T-SQL UDF of the Week Newsletter. I'm going to take the holiday week off and retool for the next volume. The title of the newsletter is going to change but I still don't know the new title. So don't be surprised by the new title. Before that happens please add novicksoftware.com to your safe senders domains in your spam blocker.

The new newsletter is going to expand the scope of what it covers. There will still be plenty of UDFs and UDF related topics but it's going to include more topics on stored procedures, triggers, and code written in .Net. The switch is inevitable, the production version of SQL Server 2005 will arrive some time in 2005.

Before I go, I'd like to wish you Happy Holidays and a Joyous New Year.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick