DSQLT (Dynamic SQL Templates) is a extension library for MS SQL Server 2005/2008 programmed in T-SQL .
Its main purposes are:
Parameter substitution (eg for SQL objects) stored procedures.
• Code generation
• easy to use schema information

The templating engine for stored procedures can replace the parameters @0 - @9 with any content and thus creates static SQL from reusable templates.
This feature in combination with iterating over a cursor resultset for replacing the template parameters awards DSQLT as a powerful tool for mass generation of T-SQL code and simple use and less error-prone dynamic SQL execution.

A DSQLT-template is a simple stored procedure, which must satisfy a few conventions.
• The name starts with an @ sign to distinguish them from normal stored procedures.
• The outermost BEGIN / END block surrounds the actual template
• Within the template, the parameters @0 - @9 could be replaced by any content (usually a object name or part of the syntax of a SQL command, eg, a list of columns).

A typical example:

CREATE PROCEDURE [Sample].[@CopyTableFrom] 
AS RETURN -- protects against accidental template call
BEGIN
TRUNCATE TABLE [@1].[@2]
INSERT INTO [@1].[@2]
SELECT * FROM [@3].[@1].[@2]
END


This template fills a table with the content of the same table from another database.
  • Single call for one table
EXEC DSQLT.[Execute] '[Sample].[@CopyTableFrom]','Sales','Customer','Adventureworks'
  • Iteration over many tables, eg all tables in the schema sales 
DECLARE @Cursor CURSOR ; SET @Cursor= CURSOR LOCAL STATIC FOR 
SELECT [Schema],[Table] FROM DSQLT.Tables('Sales.%') 
EXEC DSQLT.Iterate '[Sample].[@CopyTableFrom]',@Cursor,'Adventureworks'

[Template Execution]

Last edited Feb 24, 2011 at 3:39 PM by HenrikBauer, version 11

Comments

No comments yet.