执行一组存储过程

I am trying to create a stored procedure to execute all the stored procedures in a schema. This is what I have:

我正在尝试创建一个存储过程来执行模式中的所有存储过程。这就是我所拥有的:

ALTER procedure [ALL_IC].[EXECUTE_ICS]
    @sql nvarchar(max) = null, 
    @fa nvarchar(max) = null
as 
begin
    set @sql = ('select (select ''EXEC [IC].['' + b.name + ''];''
                         from sys.procedures b  
                         join sys.schemas s on s.schema_id = b.schema_id
                         where s.name = ''IC''
                         for xml path(''''))'
                        ) end 

    EXECUTE sp_executesql @sql

Running this selects the string to execute all the stored procedures, but it doesn't actually execute them.

运行它选择字符串来执行所有存储过程,但它实际上并不执行它们。

EXEC ALL_IC.EXECUTE_ICS

How can I actually execute all the stored procedures by running that line of code?

如何通过运行该代码行来实际执行所有存储过程?

SQL Server 2012

2 个解决方案

#1

Aside from the numerous awful things this brings to mind the actual logic is quite simple. You just need to build a dynamic string and execute it.

除了令人想到的众多可怕的事情,实际的逻辑非常简单。您只需构建一个动态字符串并执行它。

Here is a very simple way you can do this.

这是一种非常简单的方法。

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'EXEC ' + quotename(s.name) + '.' + quotename(b.name) + ';'
from sys.procedures b
join sys.schemas s on s.schema_id = b.schema_id
where s.name = 'IC'

select @SQL
--exec sp_executesql @SQL

--EDIT--

Changed slightly so the schema is not hard coded inside the dynamic sql. This way if you want a different schema you just change the schema name and everything else will still work.

略有改动,因此架构在动态sql中没有硬编码。这样,如果您想要一个不同的模式,您只需更改模式名称,其他一切仍然有效。

--Second Edit--

Changed to use QuoteName instead of hardcoding in the []. This is more flexible and stable.

更改为使用QuoteName而不是[]中的硬编码。这更灵活,更稳定。

#2

Just to make sure you are not executing any procedures that expect a parameter, I added a little check against the system catalogue sys.parameters. The rest is pretty much the same what you were trying to do in your question.

为了确保您没有执行任何期望参数的过程,我在系统目录sys.parameters上添加了一些检查。其余几乎与您在问题中尝试的相同。

Declare @sql NVARCHAR(MAX);


SELECT @sql = STUFF((SELECT '  Exec ' + QUOTENAME(s.name) +'.'+ QUOTENAME(p.name) + ';  '
                FROM sys.procedures P 
                INNER JOIN sys.schemas S ON P.schema_id = S.schema_id
                WHERE s.name = 'IC' 
                 AND NOT EXISTS (SELECT 1 
                                  FROM sys.parameters pm
                                  WHERE p.object_id = pm.object_id)
                 FOR XML PATH('')),1,2,'');

Exec sp_executesql @sql

#1