Post reply

Generate Script options

  • July 9, 2010 at 3:26 am

    #223076

    When generating a script from a stored procedure or function, they are created as EXEC dbo.sp_executesql statements inside text literals - as follows:

    /****** Object: StoredProcedure [dbo].[uspCQStats] Script Date: 07/09/2010 10:01:30 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetStats]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'

    ALTER PROCEDURE [dbo].[uspGetStats]

    @Indicator Varchar(20)

    ,@DataYear int

    --, @MinCount int = 1

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    SELECT

    *

    FROM

    vwUncollatedAnswers

    WHERE

    (DataYear = @DataYear) AND (IndicatorCode = @Indicator)

    END'

    END

    Now I am sure that I used to get them as plain scripts but can't find an option in my Management Studio setup to switch the behaviour. What am I missing?

  • July 9, 2010 at 5:35 am

    #1190707

    Oh man I hated that too;

    it's not obvious, but under Scripting options, change "Include IF NOT EXISTS Clause" from true to false...then all your objects will script normally instead of as an execute.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 9, 2010 at 5:40 am

    #1190710

    Bingo! Thanks for that!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply