如何在创建存储过程后执行它?

I'm trying to execute a stored procedure directly after its creation however it is not getting called. It looks like the stored procedure is not yet created during the execution call.

我试图在存储过程创建之后直接执行它，但是它不会被调用。看起来存储过程还没有在执行调用期间创建。

Here is how the script looks like:

剧本是这样的:

CREATE PROCEDURE sp_Transfer_RegionData
AS
BEGIN
    INSERT INTO Region (regionName)
    SELECT column1
    FROM openquery(ITDB, 'select * from db.table1')
END

EXEC sp_Transfer_RegionData

The script runs fine however the needed table is not populated. After replacing the execution part with:

脚本运行良好，但是所需的表没有填充。执行部分替换为:

IF OBJECT_ID('sp_Transfer_RegionData') IS NOT NULL
begin

exec [dbo].[sp_Transfer_RegionData]
print 'tada'
end

I could see that the stored procedure does not exist when it has to be executed. Couldn't find a solution for this in the internet...

我可以看到存储过程在必须执行时并不存在。在互联网上找不到解决办法……

So how to make the SQL script run sync so that the stored procedure would already exist during the execution part?

那么，如何使SQL脚本运行同步，以便在执行部分中已经存在存储过程?

1 个解决方案

#1

You need a GO after you created the SP otherwise you have created a recursive SP that calls itself "indefinitely" which is 32 times in SQL Server.

在创建SP之后，您需要尝试一下，否则您已经创建了一个递归SP，该SP调用自己“无限期”，在SQL Server中是32次。

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

最大存储过程、函数、触发器或视图嵌套级别超出(限制32)。

Try this:

试试这个:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO            

CREATE PROCEDURE sp_Transfer_RegionData
AS
BEGIN
    INSERT INTO Region (regionName)
    SELECT column1
    FROM openquery(ITDB, 'select * from db.table1')

END

GO

EXEC sp_Transfer_RegionData

#1