Stored procedure: Generate code for ad hoc data operations

This tip continues the system stored procedure series with a routine to generate T-SQL code for ad hoc data operations. Click to go directly to the sp_GenerateQuery routine or view the complete list of stored procedures.

A SQL Server DBA often needs to perform ad hoc operations on data in their databases. The tasks can typically be handled with simple T-SQL statements, but other times a more complex operation is called for – and having to manually enter all the T-SQL code necessary for such an operation is not appealing! It can be difficult to perfect the syntax, and tedious to list column names once, twice or even three times. Fortunately, useful template code can be easily generated instead of being 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 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, 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 tip 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 advanced functionality requires the use of surrogate keys as described in my recent feature.

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 feature 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.

Click for the stored procedure: sp_GenerateQuery

About the author: Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.

More information from SearchSQLServer.com