Stored procedure sp_for

Total article views: 730 | Views in the last 30 days: 8

 Rate this |   Join the discussion |  Briefcase

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.

Total article views: 730 | Views in the last 30 days: 8