T-SQL Snack: Why Does SSMS Generate Create Scripts With EXEC dbo.sp_executesql?

andyleonard — Mon, 19 Jul 2010 11:00:00 GMT

Introduction

I've been meaning to blog about this for a while and a question from a team member prompted this post (thanks Allan!)

SSMS Scripting Options

In SSMS you can script a stored procedure - right-click a stored procedure, hover over Script Stored Procedure As, hover over Create To, and select New Query Editor Window:

Sometimes the script is generated as shown:

The reason? Your scripting options (Tools-->Options-->Scripting in SSMS 2005 [shown below], Tools-->Options-->Sql Server Object Explorer-->Scripting in SSMS 2008 R2) are set to generate IF NOT EXISTS clauses:

Set this to False to generate stored procedure T-SQL that appears as shown here:

What I'd Like

I would like the If Not Exists statement and the T-SQL without the call to sp_execute. Something like:

IF NOT EXISTS (SELECT s.name + '.' + p.name
                        FROM sys.procedures p
                        INNER JOIN sys.schemas s ON s.schema_id = p.schema_id
                        WHERE s.name = 'dbo'
                          AND p.name = 'uspGetBillOfMaterials')
BEGIN
CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN...

In a recent email exchange with the SSIS Developer Team at Microsoft, I shared a bunch of things I would like in SSIS. Since I was asking for so much I included a request for a pony. Matt Masson (Blog - Twitter) sent me this image in reply:

The clincher in this image is the inset of a horse with the caption "NOT YOURS". I still laugh out loud whenever I look at this!

:{> Andy

Search results matching tags 'SQL Server', 'ssms', and 'No Pony'

T-SQL Snack: Why Does SSMS Generate Create Scripts With EXEC dbo.sp_executesql?