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.

Answer 2

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).