When I use SMO to script a procedure with the IncludeIfNotExists set to true, it first creates the procedure and then alters it. Is there a way for it to just create the procedure in a dynamic SQL style? (like in SSMS)
This is what I get from the SMO:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_a]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[proc_a] AS'
END
GO
ALTER PROCEDURE [dbo].[proc_a]
AS
BEGIN
-- etc..
END
GO
I want something like this instead:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_a]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[proc_a]
AS
BEGIN
-- etc..
END
'
END
GO
For anybody interested, using the SQL 2008 SMO assembly generates the code with Dynamic-SQL Style while using SQL 2017 generates the code with the ALTER statement.
[System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[System.Reflection.Assembly]::Load("Microsoft.SqlServer.SmoExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[System.Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[System.Reflection.Assembly]::Load("Microsoft.SqlServer.SmoExtended, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")