問題描述

我試圖在創建后直接執行存儲過程，但是它沒有被調用.在執行調用期間似乎尚未創建存儲過程.

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

這篇關于存儲過程創建后如何執行?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！