The "Include IF NOT EXISTS clause" option - by SQLScott

Status : 

  Duplicate<br /><br />
		This item appears to be a duplicate of another existing Connect or internal item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 264310 Comments
Status Resolved Workarounds
Type Suggestion Repros 0
Opened 3/20/2007 9:57:46 AM
Access Restriction Public

Description

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'))
BEGIN
EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE [dbo].[usp_ClientPayers_Get]
    @filterID int
AS
....
'
END

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. 
Sign in to post a comment.
Posted by Daniel Wojciechowski on 8/30/2011 at 7:32 AM
Wrong (will never work):
IF NOT EXISTS ... ALTER

Good:
IF EXISTS ... ALTER


Good:
IF EXISTS    DROP ...
GO
CREATE PROCEDURE ...

Maybe adding CREATE OR ALTER is a good thing, but the main issue is that SSMS should generate code that is working! As you can see above generating proper code is possible without CREATE OR ALTER syntax.
Posted by Daniel Wojciechowski on 8/30/2011 at 7:21 AM
Duplicate? It is not the request to extend T-SQL syntax (by adding "CREATE OR REPLACE"). This is about bug of SSMS. SSMS generates code that will newer work. Umachandar, see it again: IF NOT EXISTS ... ALTER PROCEDURE. It will newer work!
Posted by Microsoft on 3/24/2010 at 2:16 PM
Hi,
I have resolved this request as duplicate of feedback item below:

https://connect.microsoft.com/SQLServer/feedback/details/127219/create-or-replace

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 12/12/2007 at 12:05 PM
Hello,

Thank you for sending your feedback to us. We will consider adding 'CREATE OR ALTER' in a future release of SQL Server.

Thanks,
-Vineet Rao.
Posted by AaronBertrand on 3/20/2007 at 8:10 PM
I asked about this in 242799 (it also appears in 260224 and probably others). Looks like a similar concern, and sadly, there isn't an easy way around it because you can't put "ALTER PROCEDURE" inside a BEGIN/END block. You need the ALTER to be in its own batch, so you can't have conditional logic around it. What would be better is to have CREATE OR ALTER syntax, where you can write a single statement that creates the procedure if it doesn't exist, or alters an existing one if it does. You can vote for this syntax in 127219.