SQL Studio - "Modify Stored Procedure" Script Errors
In MS SQL Server Management Studio 2005:
If you have the Tools | Options | Scripting | "Include IF NOT EXISTS" set to true, modifying the stored procedure will generate a strange script that doesn't even work. It looks something like this: (ellipsis is used for brevity)
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS [...]
BEGIN
EXEC dbo.sp_executesql @statement = N'
ALTER procedure [dbo].[p_Procedure]
[...]
'
END
This clearly does not work because the only way the ALTER statement is called is if the stored procedure does NOT exist.
The question is, is there a way to change this generated code ? Is the template out there somewhere (it doesn't seem to be related to the assembly in the Templating tools)?
(A little explanation of this behavior: the CREATE statement script generates the same code in which IF NOT EXISTS makes more sense)
source to share
There are several questions about this topic on the MS-feedback site. Here is one of them:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=260519
Here's one comment on this issue (at the bottom of the page linked above):
In SQL2000, the approach was If exists, DROP followed by CREATE. It is flawless and covered all cases. It was so good that we built our deployment from this script model. Since SQl2005 entered our world, we have had manual, cumbersome workarounds to replace the automated scripts that were lost when migrating to SQL2000.
Please read, if exists, followed by DROP followed by CREATE. it was a great way
source to share