How to instruct SQL Server to automatically execute a batch for a certain number of times?

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***