How to execute a stored procedure after it is created?

sql, sql-server, stored-procedures, t-sql

Solution

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.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 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

Problem

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?

Original source