Check for object existence sql server 2012 is creating stored procedure as dynamic sql

[indian143]

Hi, I wanted to create script with Check for Objects existence in SQL Server, when I have enabled that option from Options -> SQL Server Object Explorer->Check for object existence, its creating the script for the Check but Stored Procedures are created using dynamic sql as below, any help would be very very helpful thanks in advance friends.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_Add_AddressCommunication]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
/********************************************************************************************************/
/* NAME : [Usp_Add_AddressCommunication] */
/* PURPOSE : THIS STORED PROCEDURE Addes address communication */
/* TABLES USED: */
/* [dbo].[CommunicationTypeLKP] , AddressCommunication , Address */
/* VERSION HISTORY:- */

/* VERSION NUMBER| DATE | AUTHOR | CHANGES */

/* 1.0 | 09/19/2017 | aaleti | INITIAL VERSION */

---

ALTER PROCEDURE [dbo].[Usp_Add_AddressCommunication]
(

@AddressId int,
@CommunicationType varchar(60),
@CommunicationValue varchar(60),
@CreatedBy varchar(30)

)

AS
BEGIN
DECLARE @CommTypeId int
SET @CommTypeId = (Select top 1 CT.PKCommunicationTypeLKPId from CommunicationTypeLKP CT where CT.CommunicationTypeDesc = @CommunicationType)

	 INSERT INTO \[dbo\].\[AddressCommunication\]
       (\[FKAddressId\]
       ,\[FKCommunicationTypeLKPId\]
       ,\[CommunicationValue\]
       ,\[ValidFlag\]
       ,\[CreatedDate\]
       ,\[CreatedBy\]
       ,\[ModifiedDate\]
       ,\[ModifiedBy\]
       )
 VALUES
       (@AddressId
       ,@CommTypeId
       ,@CommunicationValue
       ,1
       ,getdate()
       ,@CreatedBy
       ,getdate()
       ,@CreatedBy)
	    select SCOPE\_IDENTITY() as PkAddrCommId

END
'
END

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

[Richard Deeming]

Help with what? That's just the way that feature works. Prior to SQL Server 2016 SP1[^], there's no CREATE OR ALTER PROCEDURE statement. And as the documentation[^] says: "The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch." Which means there's no way to do a CREATE / ALTER within an If block. So the only option is to execute a string. If you're willing to manually change the output, you can reduce the duplication slightly:

DECLARE @statement nvarchar(MAX) = N'/****************************************************************************************************/
...
';

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Usp_Add_AddressCommunication]'))
BEGIN
SET @statement = @statement + N'CREATE ';
END
Else
BEGIN
SET @statement = @statement + N'ALTER ';
END;

SET @statement = @statement + N'PROC [dbo].[Usp_Add_AddressCommunication]
...';

EXEC dbo.sp_executesql @statement = @statement;

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer