please help !! Dynamic SQL

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 8, 2018 at 2:15 am

  #2000743

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  August 8, 2018 at 6:14 am

  #2000763

  Sue_H - Tuesday, August 7, 2018 5:17 PM

  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

  Opening the file in something as unevolved as Notepad will show the effect of missing CR characters, but I rarely use that for SQL scripts.  Opening the file in something more intelligent like TextPad will display all three as two lines.  If you open it in Management Studio, not only will it ignore the missing CR characters but it will offer to add them for you.  If you open it in Notepad and see the first two examples as single lines, you can copy-and-paste the text into Management Studio and all three examples will display as two lines.  If you paste it in an Outlook email they are all two lines.  So when I asked about where the CR would be required, I think a requirement that the resulting SQL script display correctly in Notepad would be pretty rare.

  This code is generating text that will be interpreted as a SQL command or saved as a SQL script. In that context the missing carriage returns have no effect.  If you were generating text to output to a file, that would be consumed by something likely to get confused by missing CRs, then put them in.  It is still my contention that for anything creating dynamic SQL, " + CHAR(13)" is just useless noise. 

  Not to mention that if you use a multiline literal, you don't need to explicitly define the line breaks.  Now that SQL Server runs on Linux, do you want to add code to choose between CR/LF or LF depending on environment?  Or would you be better off just showing that a line break is required and letting the system worry about what that should be?

  SELECT 'Line1
Two'

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  August 8, 2018 at 6:26 am

  #2000766

  Jeff Moden - Tuesday, August 7, 2018 8:56 PM

  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.

  Yes, most files in the Windows world will have the standard CR/LF EOL sequence.  That doesn't make the CR characters indispensable when generating dynamic SQL .  When interpreting T-SQL, there's no significant difference (outside of string literals) between CR, LF, CR/LF, or just a space.  You put line breaks and indents in to make code readable for humans.  But I find expressions full of " + CHAR(13) + CHAR(10) + " are much less readable than multiline literals.

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  August 8, 2018 at 6:57 am

  #2000782

  Scott Coleman - Wednesday, August 8, 2018 6:26 AM

  Jeff Moden - Tuesday, August 7, 2018 8:56 PM

  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.

  Yes, most files in the Windows world will have the standard CR/LF EOL sequence.  That doesn't make the CR characters indispensable when generating dynamic SQL .  When interpreting T-SQL, there's no significant difference (outside of string literals) between CR, LF, CR/LF, or just a space.  You put line breaks and indents in to make code readable for humans.  But I find expressions full of " + CHAR(13) + CHAR(10) + " are much less readable than multiline literals.

  Heh... you've shifted gears pretty hard.  Your question was originally that you'd like to know what environment they happen in.  Now you're talking about whether or not the characters are indispensable or not.  Almost nothing is indispensable in the world of data except accuracy and correctness and some people even argue against that.

  Totally agreed on the "no significant difference" thing but lot's of apps put out the ^C_R^L_F thing.  I also typically don't use the "+CHAR(13)+CHAR(10)" bit of code in expressions when generating things in T-SQL.  I just make a tokenized template the same way that I type anything else and put quotes around it.  The ^C_R^L_F's happen naturally that way. The tokenization also eliminates the need for the '+@SomeVariable+' stuff that a lot of people use.  You do have to be aware of SQL Injection and prevent it if you use such things or use sp_ExecuteSQL properly when variables can be used.

  To be honest though, I think the whole human-readable thing is a bit on the stupid side because it's not usually humans that need to read data directly from a file.  In pure ASCII data transmissions, I'd like to see the use of ASCII characters 28 through 31 come back into favor because it's highly resistant to the common problems experienced with CSV, TSV, and other data transmission formats. 

  Heh... you're crabbing about ^C_R^L_F's .  Consider what XML does.  I'd love to see XML simply go away.  People say it's "indespensible" but that's only because people think it is and want to continue to have humans be able to read a file directly.  Huge waste of clock cycles and space, IMHO.

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

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  August 8, 2018 at 7:38 am

  #2000794

  Just to throw mt 2 cents into the mix...
  Complex dynamic sql can be a nightmare to debug. By simply investing a tiny bit of extra effort to add a "print" option and format the dynamic strings so that the printed output is properly formatted, you can save HUGE amouts of time debugging code. This is especially true when you need to modify the code at some point in the future. Plus, it shows that you take some pride in your craft.

  Here's an example I posted a a couple days ago...https://www.sqlservercentral.com/Forums/FindPost1983383.aspx
  Note that while I didn't use "CHAR(10) + CHAR(13)", I did infact include CRLF's & TAB's to achieve the desired output formatting.
  Setting the @_debug = 1, on that code, yields the following:

  IF OBJECT_ID('tempdb..#NamePos', 'U') IS NOT NULL
BEGIN DROP TABLE #NamePos; END;

  SELECT
  td.Name,
  td.Amount,
  rn = ISNULL(ROW_NUMBER() OVER (PARTITION BY td.Amount ORDER BY td.Name), 0)
  INTO #NamePos
FROM
  dbo.TestData td;

  ALTER TABLE #NamePos ADD PRIMARY KEY CLUSTERED (rn, Amount);

  SELECT
  [100] = MAX(CASE WHEN np.Amount = 100.00 THEN np.Name END),
  [150] = MAX(CASE WHEN np.Amount = 150.00 THEN np.Name END),
  [200] = MAX(CASE WHEN np.Amount = 200.00 THEN np.Name END),
  [290] = MAX(CASE WHEN np.Amount = 290.00 THEN np.Name END)
FROM
  #NamePos np
GROUP BY
  np.rn;

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  August 8, 2018 at 8:05 am

  #2000807

  laurie-789651 - Wednesday, August 8, 2018 2:15 AM

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

  Sorry your post went off the subject. Back to your question -
  Try putting in a GO to separate the batches - just add it in this section:
  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
GO

  Sue

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  August 8, 2018 at 8:14 am

  #2000810

  Sue_H - Wednesday, August 8, 2018 8:05 AM

  laurie-789651 - Wednesday, August 8, 2018 2:15 AM

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

  Sorry your post went off the subject. Back to your question -
  Try putting in a GO to separate the batches - just add it in this section:
  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
GO

  Sue

  Just keep in mind that GO will not work in dynamic SQL that is contained by and executed from a variable.

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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 8, 2018 at 8:16 am

  #2000812

  laurie-789651 - Wednesday, August 8, 2018 2:15 AM

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

  Nope, give me a few minutes.

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  August 8, 2018 at 8:20 am

  #2000813

  Sue_H - Wednesday, August 8, 2018 8:05 AM

  laurie-789651 - Wednesday, August 8, 2018 2:15 AM

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

  Sorry your post went off the subject. Back to your question -
  Try putting in a GO to separate the batches - just add it in this section:
  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
GO

  Sue

  That won't work either... GO is a SSMS batch separator and isn't recognized as legit T-SQL by the database itself. 
  Including "GO", as a batch separator, in dynamic sql will casue an error every time.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 8, 2018 at 8:23 am

  #2000815

  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].[sp_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
  

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  August 8, 2018 at 8:26 am

  #2000818

  Jason A. Long - Wednesday, August 8, 2018 8:20 AM

  Sue_H - Wednesday, August 8, 2018 8:05 AM

  laurie-789651 - Wednesday, August 8, 2018 2:15 AM

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

  Sorry your post went off the subject. Back to your question -
  Try putting in a GO to separate the batches - just add it in this section:
  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
GO

  Sue

  That won't work either... GO is a SSMS batch separator and isn't recognized as legit T-SQL by the database itself. 
  Including "GO", as a batch separator, in dynamic sql will casue an error every time.

  Yup...thanks, forgot. So the alternative is what?

  Sue

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  August 8, 2018 at 8:29 am

  #2000820

  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
  

  Thanks for posting that - should work for the poster.

  Sue

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  August 8, 2018 at 8:43 am

  #2000824

  Sue_H - Wednesday, August 8, 2018 8:26 AM

  Jason A. Long - Wednesday, August 8, 2018 8:20 AM

  Sue_H - Wednesday, August 8, 2018 8:05 AM

  laurie-789651 - Wednesday, August 8, 2018 2:15 AM

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

  Sorry your post went off the subject. Back to your question -
  Try putting in a GO to separate the batches - just add it in this section:
  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
GO

  Sue

  That won't work either... GO is a SSMS batch separator and isn't recognized as legit T-SQL by the database itself. 
  Including "GO", as a batch separator, in dynamic sql will casue an error every time.

  Yup...thanks, forgot. So the alternative is what?

  Sue

  Two or more separate executions of dynamic SQL if drops clash with other code.

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

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  August 8, 2018 at 8:48 am

  #2000829

  Jeff Moden - Wednesday, August 8, 2018 8:43 AM

  Sue_H - Wednesday, August 8, 2018 8:26 AM

  Jason A. Long - Wednesday, August 8, 2018 8:20 AM

  Sue_H - Wednesday, August 8, 2018 8:05 AM

  laurie-789651 - Wednesday, August 8, 2018 2:15 AM

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

  Sorry your post went off the subject. Back to your question -
  Try putting in a GO to separate the batches - just add it in this section:
  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
GO

  Sue

  That won't work either... GO is a SSMS batch separator and isn't recognized as legit T-SQL by the database itself. 
  Including "GO", as a batch separator, in dynamic sql will casue an error every time.

  Yup...thanks, forgot. So the alternative is what?

  Sue

  Two or more separate executions of dynamic SQL if drops clash with other code.

  Yup I know. Lynn posted it - I just am quite a bit swamped at the moment and didn't have time to write up an example.
  Sorry about that - just didn't want the posters question to be missed.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 8, 2018 at 8:59 am

  #2000833

  Sue_H - Wednesday, August 8, 2018 8:48 AM

  Jeff Moden - Wednesday, August 8, 2018 8:43 AM

  Sue_H - Wednesday, August 8, 2018 8:26 AM

  Jason A. Long - Wednesday, August 8, 2018 8:20 AM

  Sue_H - Wednesday, August 8, 2018 8:05 AM

  laurie-789651 - Wednesday, August 8, 2018 2:15 AM

  Lynn Pettis - Tuesday, August 7, 2018 9:59 AM

  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
  

  I'm getting a problem running this SQL.
  Error message:

  Msg 111, Level 15, State 1, Line 9
  'CREATE VIEW' must be the first statement in a query batch.

  Am I missing something?

  Sorry your post went off the subject. Back to your question -
  Try putting in a GO to separate the batches - just add it in this section:
  IF OBJECT_ID(''!ViewName!'') IS NOT NULL
DROP VIEW !ViewName!;
GO

  Sue

  That won't work either... GO is a SSMS batch separator and isn't recognized as legit T-SQL by the database itself. 
  Including "GO", as a batch separator, in dynamic sql will casue an error every time.

  Yup...thanks, forgot. So the alternative is what?

  Sue

  Two or more separate executions of dynamic SQL if drops clash with other code.

  Yup I know. Lynn posted it - I just am quite a bit swamped at the moment and didn't have time to write up an example.
  Sorry about that - just didn't want the posters question to be missed.

  Updated my code, forgot the sp_ in the dynamic part of the code. Guess that is what happens while multitasking at times.