Technical Article

Stored procedure sp_for

,

The sp_for procedure provides simple single-statement loop processing for T-SQL statements, similar to the FOR command in the C language or the DOS command-line utility by the same name.

It's syntax is similar to that of the DOS command.  It takes an argument to represent a variable, initialize the variable, set an increment, set an end point, and a T-SQL command to run during the duration of the loop.

EXEC sp_for '@i', 1, 1, 5, 'PRINT OBJECT_NAME(@i)'

The above example would print the object names of all objects in the current database with an id between 1 and 5.  Although this would not be the most efficient way to do this specific operation (a set-oriented approach whould be better here), I have included this merely as an example.

/*Run this in the master database*/
CREATE PROCEDURE sp_for
@Variable sysname,
@Start int,
@Increment int,
@Stop int,
@Command nvarchar(4000)

as

DECLARE @sql nvarchar(4000)

SET @sql = 
'
DECLARE ' + @Variable + ' sysname

SET ' + @Variable + ' = ' + CAST(@Start AS varchar) + '


WHILE ' + @Variable + ' <= ' + CAST(@Stop as varchar) + '
BEGIN

' + @Command + '

SET ' + @Variable + ' = ' + @Variable + ' + ' + 
        CAST(@Increment as varchar) + '

END'

EXEC sp_executesql @sql

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating