CODEWITHSUNDEEP
sql-server
Mark
Mark

Reputation: 1519

Why does sql server generate stored procedures using sp_executesql statement with string?

When I generate sql schema creation scripts manually I usually just call 'Create Procedure...', however I notice that when you generate the script using the Tasks/Generate Scripts option it uses 'spexecutesql @statement = ..' e.g.

    EXEC dbo.sp_executesql @statement = N'-- =============================================
    -- Author:      Me
    -- Create date: 20/03/2009
    -- Description: Does stuff
    -- =============================================
    CREATE PROCEDURE [dbo].[MyProc]
        -- Add the parameters for the stored procedure here
        @StartDate datetime
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    ...
END
'

Why is this? Is it something about retaining comments? Thanks

Upvotes: 14

Views: 9644

Answers (5)

Michael Haren
Michael Haren

Reputation: 108336

It has nothing to do with comments. It does it that way only when you tell it to "include IF NOT EXISTS". The reason is that it can only programmatically include or exclude objects if they are executed dynamically.

You can disable this is stored procedures by selecting "False" in Options\SQL Server Object Explorer\Scripting - Check for object existence.

Upvotes: 22

xiaoyifang
xiaoyifang

Reputation: 959

sql server 2012,set Tools=>Options=>SQL Server Object Explore=>Scripting ,check object existence = false

can solve this problem.

Upvotes: 3

Thomas McNamee
Thomas McNamee

Reputation: 712

I realize this is old, but the fix is buried pretty deep in Sql 2012. Michael Haren is right, the method of rendering sprocs changes when Object Existence Checks are required in Options. To change this, go to Options, Sql Server Object Explorer, Scripting, Object Scripting Options, and set 'Check for object existence' to false. Sprocs now render 'normally', without using sp_executesql.

Upvotes: 4

Chains
Chains

Reputation: 13157

Using spexecutesql is a best-practice. Has to do with preventing sql injection, etc. by isolating / limiting the scope of variables, etc. More here: http://msdn.microsoft.com/en-us/library/ms188001.aspx

You don't HAVE to use spexecutesql -- EXEC works too -- a lot of folks just use plain old EXEC -- it's just not as safe.

Upvotes: 0

ahains
ahains

Reputation: 1912

I would guess it has to do with being able to create multiple sprocs in the same script file without GO's? If you do a create sproc ... directly, you have to complete it in a batch (finish with a GO). With the sp_executesql you shouldn't have to have a go in the generated scripts between objects. Although I don't remember, maybe there is one there anyhow.. (don't have a db in front of me).

Upvotes: 0

Related Questions