This can be done using a simple parameter of GO. GO is a command recognized by SQL Server tools (sqlcmd and osql or client utilities). This command is interpreted by the tools as a signal to send to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.
From SQL 2005 onwards, you can pass a positive integer value with GO which will further instruct utilities to send the same batch to an instance of SQL Server for execution for the specific time.
So if you want your batch to be executed automatically N number of times, simply put below command at the end of batch:
GO N --- N is a positive integer.
Example:
Below example automatically executes the batch for 3 times:
use tempdb
-- Start of batch
declare @var int
set @var = 1
set @var = @var + 1
print 'Statement Printed. Variable Value is:'
print @var
-- End of batch
go 3 -- Batch is executed 3 times
print '***Batch execution complete***'
Output:
Beginning execution loop
Statement Printed. Variable Value is:
2
Statement Printed. Variable Value is:
2
Statement Printed. Variable Value is:
2
Batch execution completed 3 times.
***Batch execution complete***
Comments