How can we help you today? How can we help you today?

Setting for CREATE/ALTER proc instead of sp_Executesql?

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
gwood
0

Comments

2 comments

Sort by
  • Mike U
    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.
    Mike U
    0
  • BKStrelioff
    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.
    BKStrelioff
    0

Add comment

Please sign in to leave a comment.