The "Include IF NOT EXISTS clause" option
3/20/2007 9:57:46 AM
In SQL Server 2005 SP2, the "Include IF NOT EXISTS clause" option scripts objects as follows:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ClientPayers_Get]') AND type in (N'P', N'PC'))
EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE [dbo].[usp_ClientPayers_Get]
While I really do like this option, including the "dbo.executesql @statement = N' stuff really throws the scripting for a loop.
First, if you make a change to the stored proc code and then run the script as it was generated, the changes are not made. Second, I don't understand why it scripts it like this.
For example, if you are scripting a stored procedure, and it contains something like the following:
WHERE (T_Payers.pay_ContactLastName LIKE @LastName + '%')
Then this option scripts the above line in the stored procedure as follows:
WHERE (T_Payers.pay_ContactLastName LIKE @LastName + ''%'')
Notice the extra set of quotes around the percent sign.