Home Dashboard Directory Help
Search

The "Include IF NOT EXISTS clause" option by SQLScott


Status: 

Resolved
 as Duplicate Help for as Duplicate


3
0
Sign in
to vote
Type: Suggestion
ID: 264310
Opened: 3/20/2007 9:57:46 AM
Access Restriction: Public
1
Workaround(s)
view

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.
Details
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.
Sign in to post a workaround.
Posted by Diane Sithoo on 8/9/2007 at 10:02 AM
I also hated this behaviour. However, I have confirmed that if you change your scripting options to set "Include IF NOT EXISTS clause" to False, you will be able to get regular T-SQL output for your scripts (i.e. CREATE, DROP, and ALTER TO scripts, instead of the completely unreadable sp_executesql stuff). There's some sort of bug or "behavioural issue" when you have it set to True.

If you really want the IF NOT EXISTS clause, then you can flip between these options and combine the output scripts produced.

Hope this helps