Problem
Creating or altering a stored procedure
using dynamic SQL :
DECLARE @SQLCmd NVARCHAR(MAX)
SET @SQLCmd =
'IF
OBJECT_ID(''[dbo].[StoredProcedureName]'') IS NOT NULL
DROP PROCEDURE dbo.StoredProcedureName
GO
CREATE PROCEDURE
[dbo].[StoredProcedureName]
AS
...'
EXEC(@SQLCmd)
Solved
Break the DROP and CREATE into 2
commands:
DECLARE @SQLCmd NVARCHAR(MAX)
SET @SQLCmd =
'IF
OBJECT_ID(''[dbo].[StoredProcedureName]'') IS NOT NULL
DROP PROCEDURE dbo.StoredProcedureName'
EXEC(@SQLCmd)
SET @SQLCmd =
'CREATE PROCEDURE
[dbo].[StoredProcedureName]
AS
...'
EXEC(@SQLCmd)
Or set only the CREATE command as
dynamic code:
IF OBJECT_ID(''[dbo].[StoredProcedureName]'') IS NOT NULL
DROP PROCEDURE dbo.StoredProcedureName
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @SQLCmd NVARCHAR(MAX)
SET @SQLCmd =
'CREATE PROCEDURE
[dbo].[StoredProcedureName]
AS
...'
EXEC(@SQLCmd)
