In MS SQL Server Management Studio 2005:
If you have the setting Tools|Options|Scripting|"Include IF NOT EXISTS clause" set to true, modifying a stored procedure will create a strange script that doesn't even work. It looks something like the following: (ellipsis 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 obviously doesn't work because the only way the ALTER statement is called, is if the stored procedure DOESN'T exist.
The question is thus: is there any way of changing this generated code? A template out there somewhere (this doesn't seem to be related to the build in Templating tools)?
(A slight explanation for this behavior: scripting a CREATE statement generates the same code in which the IF NOT EXISTS makes more sense)