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

2021-09-10 00:00:00 sql tsql sql-server stored-procedures

我试图在创建后直接执行存储过程，但是它没有被调用.在执行调用期间似乎尚未创建存储过程.

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.

脚本如下所示:

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

那么如何让SQL脚本同步运行，让存储过程在执行部分就已经存在了?

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

推荐答案

在创建 SP 后需要一个 GO，否则你创建了一个递归的 SP，它无限期地"调用自己，这在 SQL Server 中是 32 次.

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.

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

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

试试这个:

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

相关文章