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

Question

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

Answer 1

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.

Answer 2

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.