This tip continues the system stored procedure series with a routine to generate T-SQL code for ad hoc data operations.

A SQL Server DBA often needs to perform ad hoc operations on the data in their databases.  Many times the tasks can be handled with simple T-SQL statements, but other times a more complex operation must be performed.  It's not very appealing to have to manually enter all the T-SQL code necessary for a complex operation.  It can be difficult to get all the syntax perfectly correct.  It can be very tedious to list column names once, twice, or even three times.  Fortunately, useful template code can be easily generated instead of entered by hand.

The SQL code in Listing 1 creates a system stored procedure named sp_GenerateQuery.

The sp_GenerateQuery stored procedure accepts seven parameters (as indicated below), but only the first one is required.

CREATE PROCEDURE dbo.sp_GenerateQuery
    @DBTable varchar(100),
    @PCAdmin varchar(100)  = NULL,
    @DBAdmin int = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL

The first parameter (@DBTable) specifies the main table to be included in the query.

The second parameter (@PCAdmin) is optional and it specifies what (if any) template code will surround a core SELECT statement.  If the value is a temporary table name then a statement is included to create the table.  If the value is a table variable name (which requires SQL Server 2000 or newer) then a statement is included to declare the table.  If the value is "*" or "|" then an INSERT SELECT statement is generated.  If the value is any other set of characters then it's assumed to be a cursor name and statements are included to provide a structured cursor routine.  A cursor routine consists of local variable declarations, a cursor declaration, and a processing loop with appropriate error checking.

The third parameter (@DBAdmin) is optional and it specifies whether parent tables should be joined to the main table in the query.  A value of zero (0) means no parent tables are included, and that's the default behavior.  A value of one (1) means all parent tables are included.  A value of two (2) means any parent tables and ancestor tables specified by the last four parameters are included.

The last four parameters are optional and they work together to form a combination of selection criteria using table names.  Please refer to my earlier <A HREF="http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1109458,00.html">tip</A> for an explanation of how these parameters work.  These parameters specify the tables to be joined to the main table (@DBTable) when the value of @DBAdmin is two (2).  If ancestor tables are indicated then all intermediate tables must be indicated as well.  If a foreign key is mandatory then an INNER JOIN is used.  If a foreign key is optional (the column accepts a NULL value) then an OUTER JOIN is used.  This feature requires the use of surrogate keys as decribed in my <A HREF="http://searchsqlserver.techtarget.com/generic/1,295582,sid87_gci1156602_tax301329,00.html">article</A>.

The sp_GenerateQuery stored procedure generates T-SQL code to query the @DBTable table.  A core SELECT statement is always generated, and it can be surrounded by a variety of template code as determined by the other parameters.  The generated T-SQL code can be copied with two keystrokes, pasted into a new Query Analyzer window, and executed.  The code is designed and formatted for easy modification.

The sp_GenerateQuery stored procedure contains a local variable, @TPre, that can be used to specify a table name prefix.  If a table name prefix is identified with this variable then the prefix can be omitted from table names provided as parameters.

My surrogate key architecture <A HREF="http://searchsqlserver.techtarget.com/generic/1,295582,sid87_gci1156602_tax301329,00.html">article</A> includes an example database.  The last two examples below are intended to be executed in the context of that database, which uses the required surrogate keys.

This example generates a simple SELECT statement for the Customers table in the Northwind database.

EXECUTE sp_GenerateQuery 'Customers'

This example generates an INSERT SELECT statement using the Customers table in the Northwind database.

EXECUTE sp_GenerateQuery 'Customers','*'

This example generates an INSERT SELECT statement, with data going into a temporary table, using the Customers table in the Northwind database.

EXECUTE sp_GenerateQuery 'Customers','#Work'

This example generates a template for a cursor loop using the Customers table in the Northwind database.

EXECUTE sp_GenerateQuery 'Customers','Customers'

This example generates a SELECT statement, including parent tables, for the Purchase table in my example database.

EXECUTE sp_GenerateQuery 'Purchase',NULL,1

This example generates a SELECT statement, including parent and ancestor tables, for the PurchaseItem table in my example database.

EXECUTE sp_GenerateQuery 'PurchaseItem',NULL,2,NULL,'Region'

I hope you find this system stored procedure to be useful.