CODEWITHSUNDEEP
sql-serversql-server-2008
Cute
Cute

Reputation: 14011

how to execute a stored procedure continuously?

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

Upvotes: 0

Views: 3346

Answers (3)

KM.
KM.

Reputation: 103607

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_

Upvotes: 2

Kirtan
Kirtan

Reputation: 21695

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

Upvotes: 3

JP Alioto
JP Alioto

Reputation: 45127

You can use ...

exec sprocName
GO 100

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

GO [COUNT]

See, MSDN.

Upvotes: 3

Related Questions