What are the challenges you face when working across database platforms? Take the survey
Options

Setting for CREATE/ALTER proc instead of sp_Executesql?

gwoodgwood Posts: 2
edited August 26, 2015 7:14PM in SQL Compare 11
Hi all
Have used earlier version SQL Compare in the past, and know that it generated Create/Alter proc statements in "native" format - straight Create and Alter commands.
Testing V11 is generating these commands within "red code", using sp_executesql.
i.e.
IF OBJECT_ID(N'[Archive].[MYProc]', 'P') IS NOT NULL
EXEC sp_executesql N'
ALTER Procedure [Archive].[MYProc] . . . .
END

'

Would like to have the script default to

IF OBJECT_ID(N'[Archive].[MYProc]', 'P') IS NOT NULL
ALTER Procedure [Archive].[MYProc] . . . .
END

Don't see an option to make this happen. AM I missing it?

Thanks

Comments

  • Options
    It's the object existence check that is forcing SQL Compare to use sp_executesql. CREATE/ALTER PROCEDURE has to be the first statement in a block, so it can't follow a conditional. If you try it in SSMS, you'll get a syntax error.

    If you turn off the 'Add object existence checks' option, you'll get the straight CREATE/ALTER behaviour back.
    Development Lead
    Redgate Software
  • Options
    An option to generate existential DROP, then always CREATE would be nice. It allows scripting of "new" stuff to get rid of the old, and has the CREATE body in real SQL rather than in an executed string.
Sign In or Register to comment.