please help !! Dynamic SQL

• Lidou123

  Hall of Fame

  Points: 3186

  More actions

  August 6, 2018 at 6:22 pm

  #357260

  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: 1004406

  More actions

  August 6, 2018 at 7:13 pm

  #2000529

  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.
  

  ---

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

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  August 6, 2018 at 7:14 pm

  #2000530

  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.
  

  ---

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

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 7, 2018 at 8:45 am

  #2000620

  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: 3186

  More actions

  August 7, 2018 at 8:55 am

  #2000624

  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: 1004406

  More actions

  August 7, 2018 at 9:07 am

  #2000625

  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.
  

  ---

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

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 7, 2018 at 9:25 am

  #2000631

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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 7, 2018 at 9:31 am

  #2000634

  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: 3186

  More actions

  August 7, 2018 at 9:47 am

  #2000637

  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: 442467

  More actions

  August 7, 2018 at 9:59 am

  #2000646

  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: 27452

  More actions

  August 7, 2018 at 11:58 am

  #2000679

  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: 442467

  More actions

  August 7, 2018 at 12:04 pm

  #2000683

  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: 27452

  More actions

  August 7, 2018 at 1:59 pm

  #2000706

  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: 90860

  More actions

  August 7, 2018 at 5:17 pm

  #2000719

  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: 1004406

  More actions

  August 7, 2018 at 8:56 pm

  #2000730

  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' ^C_R^L_F (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.
  

  ---

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