please help !! Dynamic SQL

  • laurie-789651

    SSCertifiable

    Points: 7680

    Lynn Pettis - Wednesday, August 8, 2018 8:23 AM

    Try this, uncomment the EXEC statement.

    DECLARE @SQLTemplate NVARCHAR(MAX)
            , @SQLCmd NVARCHAR(MAX)
            , @SQLParm NVARCHAR(MAX) = N'@iViewDef NVARCHAR(MAX)'
            , @ViewName NVARCHAR(256)
            , @ViewDef NVARCHAR(MAX)
            , @DBName NVARCHAR(256);

    SET @DBName = QUOTENAME(N'Test');
    SET @SQLTemplate = N'
    USE !DBName!;

    IF OBJECT_ID(''!ViewName!'') IS NOT NULL
      DROP VIEW !ViewName!;

    exec [sys].[executesql] @iViewDef
    ';

    DECLARE [ViewDef] CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
    FOR
    SELECT
      QUOTENAME(OBJECT_SCHEMA_NAME([v].[object_id])) + '.' + QUOTENAME([v].[name]) AS [Viewname]
      , [sm].[definition]                   AS [ViewDefinition]
    FROM
      [sys].[views]                  AS [v]
      INNER JOIN [sys].[sql_modules] AS [sm]
        ON [sm].[object_id] = [v].[object_id]
    ORDER BY
      [v].[name];

    OPEN [ViewDef];

    WHILE 1 = 1
    BEGIN
      FETCH NEXT FROM [ViewDef]
      INTO @ViewName, @ViewDef;

      IF @@FETCH_STATUS <> 0 BREAK; -- Exit loop on error, should enhance and use try catch

      SELECT @SQLCmd = REPLACE(REPLACE(REPLACE(@SQLTemplate,'!ViewName!',@ViewName),'!ViewDef!',@ViewDef),'!DBName!',@DBName)
      PRINT @SQLCmd;
      --EXEC [sys].[sp_executesql] @SQLCmd, @SQLParm, @iViewDef = @ViewDef;
    END

    CLOSE [ViewDef];

    DEALLOCATE [ViewDef];
    GO

    Sorry to nitpick, but -

    You need "exec [sys].[sp_executesql] @iViewDef" instead of "exec [sys].[executesql] @iViewDef".

    Other than that it's great! Hope the OP likes it...

  • Lynn Pettis

    SSC Guru

    Points: 442332

    laurie-789651 - Wednesday, August 8, 2018 9:03 AM

    Lynn Pettis - Wednesday, August 8, 2018 8:23 AM

    Try this, uncomment the EXEC statement.

    DECLARE @SQLTemplate NVARCHAR(MAX)
            , @SQLCmd NVARCHAR(MAX)
            , @SQLParm NVARCHAR(MAX) = N'@iViewDef NVARCHAR(MAX)'
            , @ViewName NVARCHAR(256)
            , @ViewDef NVARCHAR(MAX)
            , @DBName NVARCHAR(256);

    SET @DBName = QUOTENAME(N'Test');
    SET @SQLTemplate = N'
    USE !DBName!;

    IF OBJECT_ID(''!ViewName!'') IS NOT NULL
      DROP VIEW !ViewName!;

    exec [sys].[executesql] @iViewDef
    ';

    DECLARE [ViewDef] CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
    FOR
    SELECT
      QUOTENAME(OBJECT_SCHEMA_NAME([v].[object_id])) + '.' + QUOTENAME([v].[name]) AS [Viewname]
      , [sm].[definition]                   AS [ViewDefinition]
    FROM
      [sys].[views]                  AS [v]
      INNER JOIN [sys].[sql_modules] AS [sm]
        ON [sm].[object_id] = [v].[object_id]
    ORDER BY
      [v].[name];

    OPEN [ViewDef];

    WHILE 1 = 1
    BEGIN
      FETCH NEXT FROM [ViewDef]
      INTO @ViewName, @ViewDef;

      IF @@FETCH_STATUS <> 0 BREAK; -- Exit loop on error, should enhance and use try catch

      SELECT @SQLCmd = REPLACE(REPLACE(REPLACE(@SQLTemplate,'!ViewName!',@ViewName),'!ViewDef!',@ViewDef),'!DBName!',@DBName)
      PRINT @SQLCmd;
      --EXEC [sys].[sp_executesql] @SQLCmd, @SQLParm, @iViewDef = @ViewDef;
    END

    CLOSE [ViewDef];

    DEALLOCATE [ViewDef];
    GO

    Sorry to nitpick, but -

    You need "exec [sys].[sp_executesql] @iViewDef" instead of "exec [sys].[executesql] @iViewDef".

    Other than that it's great! Hope the OP likes it...

    Already fixed that.

  • Joe Torre

    SSChampion

    Points: 10246

    Lidou123 - Tuesday, August 7, 2018 9:47 AM

    laurie-789651 - Tuesday, August 7, 2018 8:45 AM

    Are you still trying to do this?
    What is the purpose?  Do you want to move views from one database to another?

    Jeff Moden - Tuesday, August 7, 2018 9:07 AM

    Lidou123 - Tuesday, August 7, 2018 8:55 AM

    laurie-789651 - Tuesday, August 7, 2018 8:45 AM

    Are you still trying to do this?
    What is the purpose?  Do you want to move views from one database to another?

    Hello.
    Yes. Still doing it.
    Do u have something to help me ?

    If you're trying to move the whole database, why not just do a restore of the existing database?

    Hello
    Because I don't want to do the tasks manually.  I just want to duplicate the views and the procedures.

    Why not script it out in SSMS?

  • Lynn Pettis

    SSC Guru

    Points: 442332

    Joe Torre - Thursday, August 9, 2018 6:09 PM

    Lidou123 - Tuesday, August 7, 2018 9:47 AM

    laurie-789651 - Tuesday, August 7, 2018 8:45 AM

    Are you still trying to do this?
    What is the purpose?  Do you want to move views from one database to another?

    Jeff Moden - Tuesday, August 7, 2018 9:07 AM

    Lidou123 - Tuesday, August 7, 2018 8:55 AM

    laurie-789651 - Tuesday, August 7, 2018 8:45 AM

    Are you still trying to do this?
    What is the purpose?  Do you want to move views from one database to another?

    Hello.
    Yes. Still doing it.
    Do u have something to help me ?

    If you're trying to move the whole database, why not just do a restore of the existing database?

    Hello
    Because I don't want to do the tasks manually.  I just want to duplicate the views and the procedures.

    Why not script it out in SSMS?

    Because that would be a manual process?  Just guessing.

Viewing 4 posts - 31 through 34 (of 34 total)

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