DSQLT.[Iterate]

Reads the source code of a template, replaces the parameters with the values from each rowset and executes the generated SQL code.

 

[DSQLT].[Iterate]
 
@DSQLTProc sysname = null
 
, @Cursor CURSOR VARYING OUTPUT
 
, @p1 NVARCHAR (MAX) = null
 
, @p2…@p8 NVARCHAR (MAX) = null
 
, @p9 NVARCHAR (MAX) = null
 
, @Database sysname = null
 
, @Template NVARCHAR (MAX) = null OUTPUT
 
, @Create sysname = null
 
, @Once bit = 0
 
, @Print bit = 0
 

@DSQLTProc: the name of the template. This must be the name of a stored procedure defined in any schema in the current database. The name must start with @ sign. Use null, if you want to use as a template a string instead of a procedure. Then the contents of @template are used instead of a stored procedure.

@cursor: a cursor variable that should be defined with LOCAL STATIC. On the result set is iterated. Each iteration, the current column contents in their order for the parameter substitution of @ 1 - uses @ 9th
@p1-@p9: If the cursor has less than 9 columns, then these parameters are used as content for the rest of the parameter substitution. They are assigned to @0 - @9 in order. When a cursor has 7 columns, for example, then @p1 will be used for @8 and @p2 will be used for @9.

@Database: The database, the generated SQL code should be executed within. null results in the execution in the current database.
This name is also under parameter substitution, if it contains the parameter @0 - @9t.

@Template: This can be passed instead of a string template procedure. Also serves as an optional output parameter to return the generated SQL code.
@Create: The name to generate a stored procedure with includes the generated code. If a same procedure already exists, it will be replaced.
This name is also under parameter substitution, if it contains the parameter @0 - @9. This will also help to create a lot of stored procedures with one call.
@Once: If @Once = 0, each row of the rowset creates a stored procedure. If @Once = 1, the total generated SQL code is enclosed with one stored procedure. Especially useful in combination with @Create.

@Print: controls whether the generated code is executed (0) or printed (1). null results in neither happening, but instead returning the generated code in the OUTPUT Parameter @template.

Last edited Jan 20, 2011 at 11:18 PM by HenrikBauer, version 3

Comments

No comments yet.