CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Afnan Ahmad
Afnan Ahmad

Reputation: 2542

Execute multiple statements inside Stored Procedure

I am trying to execute multiple statements inside a stored procedure. It's basically a serverless architecture where I cannot generate SQL scripts and run those to generate SPs or tables. The only way I have is to run the scripts via SP or Functions. Is there any clue how can I execute bulk statements programmatically. I tried with the following SP:

SET ANSI_NULLS ON   
SET QUOTED_IDENTIFIER ON

Alter PROCEDURE [dbo].[GenerateScript]   

AS
BEGIN

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Bar1]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Bar1] 
AS
BEGIN
Select Foo from Table 
END
' 
END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Bar2]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[Bar2] 
AS
BEGIN
Select Foo from Table 
END
' 
END

END

This is producing the error below:

'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Upvotes: 1

Views: 2883

Answers (1)

Thom A
Thom A

Reputation: 95989

The problem isn't your sp_executesql commands, it's the start of your SQL (The error is literally telling you the problem). As the error tells you CREATE/ALTER PROCEDURE' must be the first statement in a query batch, however, the first command you have in your batch is SET ANSI_NULLS ON.

Add a GO between your SET and ALTER commands to start a new batch (in SSMS/sqlcmd):

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

GO
ALTER PROCEDURE [dbo].[GenerateScript] ...

Upvotes: 2

Related Questions