Generate CREATE ALTER Procedure Script, not DROP CREATE

GurpreetGill

Hi,

By default, when generating baseline script, all the Stored Procedures are scripted as DROP and CREATE

<br><div>IF OBJECT_ID('[dbo].[My_SP]') IS NOT NULL</div><div>	DROP PROCEDURE [dbo].[My_SP];</div><div><br></div><div>GO</div><div>SET QUOTED_IDENTIFIER ON</div><div>GO</div><div>SET ANSI_NULLS ON</div><div>GO</div><div><br></div><div>CREATE PROCEDURE [dbo].[My_SP]</div><div>AS<br>BEGIN</div><div>&nbsp; &nbsp; &nbsp; &nbsp;........<br>END<br><br></div><div>GO</div>

I want to generate script as CREATE ( if not exist as empty Stored Procedure ) & ALTER

<div>IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[My_SP]') AND type in (N'P', N'PC'))</div><div>BEGIN</div><div>EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[My_SP] AS'&nbsp;</div><div>END</div><div>GO&nbsp;</div><div><br></div><div>ALTER PROCEDURE [dbo].[My_SP]&nbsp;&nbsp;</div><div>BEGIN</div><div>&nbsp; &nbsp; &nbsp; &nbsp;........</div><div>END</div><div><br></div><div>GO</div>

In DROP + CREATE all the permission Granted to Stored Procedure is lost, I need to apply them again.

In CREATE + ALTER i need not to apply permission again, because we never DROP stored Procedure.

Please help.

Thanks
Gill