please help !! Dynamic SQL

  • Lidou123

    Hall of Fame

    Points: 3052

    Hi experts

    Please I REALLY NEED YOUR HELP !!
    I want to create a dynamic sql in a cursor.
    I want to replace the database DB1 by the variable @Database
    I have lot error. I don't why !!!


    declare @SQL NVARCHAR (max)
    DECLARE @database NVARCHAR (50) = 'TEST'
    --DECLARE c CURSOR FOR
    IF OBJECT_ID('tempdb..#SQL') IS NOT NULL
      DROP TABLE #SQL

     SELECT SQL = 'IF OBJECT_ID('''
      + QUOTENAME(SCHEMA_NAME(o.[schema_id]))
      + '.' + QUOTENAME(o.name) + ''') IS NOT NULL
      BEGIN
       DROP VIEW '
       + QUOTENAME(SCHEMA_NAME(o.[schema_id]))
       + '.' + QUOTENAME(o.name) + ';
      END'
      + CHAR(13) + CHAR(10) + 'GO'
      + CHAR(13) + CHAR(10) + Definition 
        into #SQL
    FROM [DB1].sys.sql_modules AS s
    INNER JOIN [DB1].sys.objects AS o
    ON s.[object_id] = o.[object_id]
    WHERE o.type_desc = 'VIEW';
    DECLARE c CURSOR FOR
    SELECT SQL FROM #SQL

    OPEN c

    FETCH NEXT FROM c INTO @sql

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- SET @sql = REPLACE(@sql,'''','''''')
    SET @sql = ' EXEC (''' + REPLACE(REPLACE(@SQL, '''', ''''''), 'GO', '''); EXEC(''') + ''');' --'USE [' + @Name + ']; EXEC(''' + @sql + ''')'

     exec (@sql)

     FETCH NEXT FROM c INTO @sql
    END    

    CLOSE c
    DEALLOCATE c

  • Jeff Moden

    SSC Guru

    Points: 996619

    Lidou123 - Monday, August 6, 2018 6:22 PM

    Hi experts

    Please I REALLY NEED YOUR HELP !!
    I want to create a dynamic sql in a cursor.
    I want to replace the database DB1 by the variable @Database
    I have lot error. I don't why !!!


    declare @SQL NVARCHAR (max)
    DECLARE @database NVARCHAR (50) = 'TEST'
    --DECLARE c CURSOR FOR
    IF OBJECT_ID('tempdb..#SQL') IS NOT NULL
      DROP TABLE #SQL

     SELECT SQL = 'IF OBJECT_ID('''
      + QUOTENAME(SCHEMA_NAME(o.[schema_id]))
      + '.' + QUOTENAME(o.name) + ''') IS NOT NULL
      BEGIN
       DROP VIEW '
       + QUOTENAME(SCHEMA_NAME(o.[schema_id]))
       + '.' + QUOTENAME(o.name) + ';
      END'
      + CHAR(13) + CHAR(10) + 'GO'
      + CHAR(13) + CHAR(10) + Definition 
        into #SQL
    FROM [DB1].sys.sql_modules AS s
    INNER JOIN [DB1].sys.objects AS o
    ON s.[object_id] = o.[object_id]
    WHERE o.type_desc = 'VIEW';
    DECLARE c CURSOR FOR
    SELECT SQL FROM #SQL

    OPEN c

    FETCH NEXT FROM c INTO @sql

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- SET @sql = REPLACE(@sql,'''','''''')
    SET @sql = ' EXEC (''' + REPLACE(REPLACE(@SQL, '''', ''''''), 'GO', '''); EXEC(''') + ''');' --'USE [' + @Name + ']; EXEC(''' + @sql + ''')'

     exec (@sql)

     FETCH NEXT FROM c INTO @sql
    END    

    CLOSE c
    DEALLOCATE c

    Step 1 is to print out the contents of @SQL and see what you're trying to build.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Jeff Moden

    SSC Guru

    Points: 996619

    Step 2 is to realize that you can't use GO in a variable that you're going to execute.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • laurie-789651

    SSCertifiable

    Points: 7680

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

  • Lidou123

    Hall of Fame

    Points: 3052

    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 ?

  • Jeff Moden

    SSC Guru

    Points: 996619

    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?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • laurie-789651

    SSCertifiable

    Points: 7680

    What are you actually trying to do?  Are you moving views from one database to another?

  • Lynn Pettis

    SSC Guru

    Points: 442332

    To be honest, your code is confusing to me and I write a lot of dynamic SQL where I currently work due to the nature of the database I currently support.  How about you explain using words what exactly you are trying to accomplish.

  • Lidou123

    Hall of Fame

    Points: 3052

    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.

  • Lynn Pettis

    SSC Guru

    Points: 442332

    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

    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.

    You really are working too hard to accomplish what you want.
    Does this do what you expect for the views (You will need to uncomment the execution of the dynamic SQL)?  Also, it doesn't ensure the database you are attempting to USE actually exists.  That is another change that should be made.

    DECLARE @SQLTemplate NVARCHAR(MAX)
            , @SQLCmd 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!;

    !ViewDef!
    ';

    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;
    END

    CLOSE [ViewDef];

    DEALLOCATE [ViewDef];
    GO

  • Scott Coleman

    One Orange Chip

    Points: 27442

    Just to nitpick, this drives me crazy:

    SELECT 'string expression' + ';
    END'
    + CHAR(13) + CHAR(10) + 'GO'
    + CHAR(13) + CHAR(10) + Definition


    To begin with, it would be difficult to define exactly what context you need the explicit carriage return character for.  Unless you've got an old Teletype you're using as a printer.
    The other thing is that SQL is happy to deal with multiline string literals without CHAR() functions.  Even with GO lines.

    SELECT 'string expression' + ';
    END
    GO
    ' + Definition


    And as pointed out earlier, don't put GO in dynamic SQL for use with EXEC.  If you have multiple batches, create them as separate commands in separate EXEC() statements.

  • Lynn Pettis

    SSC Guru

    Points: 442332

    Scott Coleman - Tuesday, August 7, 2018 11:58 AM

    Just to nitpick, this drives me crazy:

    SELECT 'string expression' + ';
    END'
    + CHAR(13) + CHAR(10) + 'GO'
    + CHAR(13) + CHAR(10) + Definition


    To begin with, it would be difficult to define exactly what context you need the explicit carriage return character for.  Unless you've got an old Teletype you're using as a printer.
    The other thing is that SQL is happy to deal with multiline string literals without CHAR() functions.  Even with GO lines.

    SELECT 'string expression' + ';
    END
    GO
    ' + Definition


    And as pointed out earlier, don't put GO in dynamic SQL for use with EXEC.  If you have multiple batches, create them as separate commands in separate EXEC() statements.

    Depending on how I am creating my dynamic SQL I may use the + CHAR(13) + CHAR(10) (or NCHAR(13) + NCHAR(10)) but it is for human readable formatting and it does depend on what you may be using for an editor as well.  Sometimes you need both for proper formatting to occur.

  • Scott Coleman

    One Orange Chip

    Points: 27442

    It would be enlightening to hear what environment actually requires the CHAR(13).  Everything I've tried is happy with plain old CHAR(10), or is smart enough to recognize that as an EOL sequence.

  • Sue_H

    SSC Guru

    Points: 90673

    Scott Coleman - Tuesday, August 7, 2018 1:59 PM

    It would be enlightening to hear what environment actually requires the CHAR(13).  Everything I've tried is happy with plain old CHAR(10), or is smart enough to recognize that as an EOL sequence.

    Notepad.

    Output to file and open in Notepad:
    SELECT 'Line1 ' + CHAR(13) + 'Two'

    SELECT 'Line1 ' + CHAR(10) + 'Two'

    SELECT 'Line1 ' + CHAR(13) + CHAR(10) + 'Two'

    Sue

  • Jeff Moden

    SSC Guru

    Points: 996619

    Scott Coleman - Tuesday, August 7, 2018 1:59 PM

    It would be enlightening to hear what environment actually requires the CHAR(13).  Everything I've tried is happy with plain old CHAR(10), or is smart enough to recognize that as an EOL sequence.

    Most of the files I receive where I work have the ol' CRLF (CHAR(13)+CHAR(10)) line terminators.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

Viewing 15 posts - 1 through 15 (of 34 total)

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