how to execute a stored procedure continuously?

Question

I have a stored procedure that creates a database (ex: sp_createDB). In the similar fashion I have to create a 100's of databases using that one. So that I have to put it in a loop. But I don't know how to do it sqlserver. How can I do this in sqlserver.

Help me in this regard.

Thanks

Answer 1

You can use ...

exec sprocName
GO 100

See more here. In general, it's ...

GO [COUNT]

See, MSDN.

Answer 2

JP's answer is correct technically (GO [COUNT] can be used to repeat a batch of statements COUNT times), but there's a logical mistake. Your stored procedure will need different parameters everytime it executes (as it has to create unique DBs, right?), so you'll have to loop around using WHILE like this -

DECLARE @Counter INT
SET @Counter = 1

DECLARE @DBName VARCHAR(20)

WHILE (@Counter <= 100)
BEGIN
    PRINT @Counter
    @DBName = 'DB' + CAST(@Counter AS VARCHAR)
    EXEC dbo.CreateDB @DBName
END
GO

Answer 3

A while loop would be fine to call it N times as others have suggested, but...

DO NOT NAME YOUR PROCEDURES SP_ ...
DO NOT NAME YOUR PROCEDURES SP_ ...
DO NOT NAME YOUR PROCEDURES SP_ ...

Within Sql Server, "sp_ ..." is reserved for system stored procedures, and could confuse people familiar with that convention! It could cause issues if Sql Server ever implements their own "sp_createDB" Procedure. Also, stored procedures will run fractionally slower if they start with a prefix of sp_ . This is because SQL Server will look for a system stored proc first. As a result, it NOT recommend to start stored procs with a prefix of sp_