如何连续执行存储过程？

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.

我有一个创建数据库的存储过程(例如:sp_createDB)。以类似的方式,我必须使用那个创建100个数据库。所以我必须把它放在一个循环中。但我不知道怎么做sqlserver。我怎样才能在sqlserver中执行此操作。

Help me in this regard.

在这方面帮助我。

Thanks

3 个解决方案

#1

You can use ...

您可以使用 ...

exec sprocName
GO 100

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

在这里查看更多。一般来说,它是......

GO [COUNT]

See, MSDN.

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

JP的答案在技术上是正确的(GO [COUNT]可用于重复一批语句COUNT次),但是存在逻辑错误。你的存储过程每次执行时都需要不同的参数(因为它必须创建唯一的DB,对吧?),所以你必须像这样使用WHILE循环 -

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

#3

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

像其他人建议的那样,用一个while循环可以调用它N次,但......

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

不要为你的程序命名SP_ ...不要为你的程序命名SP_ ...不要为你的程序命名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_

在Sql Server中,“sp_ ...”是为系统存储过程保留的,可能会使熟悉该约定的人感到困惑!如果Sql Server实现了自己的“sp_createDB”过程,则可能会导致问题。此外,如果存储过程以sp_前缀开头,则运行速度会稍慢。这是因为SQL Server将首先查找系统存储过程。因此,不建议使用sp_前缀启动存储过程

#1