Generate CREATE ALTER Procedure Script, not DROP CREATE
GurpreetGill
Posts: 26 Bronze 1
Hi,
By default, when generating baseline script, all the Stored Procedures are scripted as DROP and CREATE
I want to generate script as CREATE ( if not exist as empty Stored Procedure ) & ALTER
In CREATE + ALTER i need not to apply permission again, because we never DROP stored Procedure.
Please help.
Thanks
Gill
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> ........<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' </div><div>END</div><div>GO </div><div><br></div><div>ALTER PROCEDURE [dbo].[My_SP] </div><div>BEGIN</div><div> ........</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
