Dynamic SQL

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71320

    Comments posted to this topic are about the item Dynamic SQL

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • HappyGeek

    SSCoach

    Points: 18661

    Good question thanks Stewart.

    ...

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Thank's the author for this question.
    In the real world, this script would end with an error. The reason is trivial. There is no scheme in the script.
    Eg. I look at a database model and in that model a table called 'Person'. There are 10 tables with that name, all in different schemas.  In that case the script would produce an error.
    But including the schema, everything works fine.

    DECLARE @schema SYSNAME= 'TheSchema', --Include schema
    ---

      SELECT
       COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_Name = @TableName
       AND TABLE_SCHEMA = @schema -- there is no schema!
       AND DATA_TYPE LIKE '%varchar';
    ---
    SELECT
     @SQL = N'SELECT @max = MAX(DATALENGTH(' + QUOTENAME(@Col) + '))
            FROM  ' + QUOTENAME(@schema) + '.' + QUOTENAME(@TableName); --schema!

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71320

    Darko Martinovic - Thursday, July 6, 2017 12:50 AM

    Thank's the author for this question.
    In the real world, this script would end with an error. The reason is trivial. There is no scheme in the script.
    Eg. I look at a database model and in that model a table called 'Person'. There are 10 tables with that name, all in different schemas.  In that case the script would produce an error.
    But including the schema, everything works fine.

    DECLARE @schema SYSNAME= 'TheSchema', --Include schema
    ---

      SELECT
       COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_Name = @TableName
       AND TABLE_SCHEMA = @schema -- there is no schema!
       AND DATA_TYPE LIKE '%varchar';
    ---
    SELECT
     @SQL = N'SELECT @max = MAX(DATALENGTH(' + QUOTENAME(@Col) + '))
            FROM  ' + QUOTENAME(@schema) + '.' + QUOTENAME(@TableName); --schema!

    Fair point (a little beside the point, but fair none the less)

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Darko Martinović

    Hall of Fame

    Points: 3535

    Stewart "Arturius" Campbell - Thursday, July 6, 2017 1:58 AM

    Darko Martinovic - Thursday, July 6, 2017 12:50 AM

    Thank's the author for this question.
    In the real world, this script would end with an error. The reason is trivial. There is no scheme in the script.
    Eg. I look at a database model and in that model a table called 'Person'. There are 10 tables with that name, all in different schemas.  In that case the script would produce an error.
    But including the schema, everything works fine.

    DECLARE @schema SYSNAME= 'TheSchema', --Include schema
    ---

      SELECT
       COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_Name = @TableName
       AND TABLE_SCHEMA = @schema -- there is no schema!
       AND DATA_TYPE LIKE '%varchar';
    ---
    SELECT
     @SQL = N'SELECT @max = MAX(DATALENGTH(' + QUOTENAME(@Col) + '))
            FROM  ' + QUOTENAME(@schema) + '.' + QUOTENAME(@TableName); --schema!

    Fair point (a little beside the point, but fair none the less)

    🙂

  • Mighty

    SSCrazy Eights

    Points: 8420

    Darko Martinovic - Thursday, July 6, 2017 12:50 AM

    Thank's the author for this question.
    In the real world, this script would end with an error. The reason is trivial. There is no scheme in the script.
    Eg. I look at a database model and in that model a table called 'Person'. There are 10 tables with that name, all in different schemas.  In that case the script would produce an error.
    But including the schema, everything works fine.

    DECLARE @schema SYSNAME= 'TheSchema', --Include schema
    ---

      SELECT
       COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_Name = @TableName
       AND TABLE_SCHEMA = @schema -- there is no schema!
       AND DATA_TYPE LIKE '%varchar';
    ---
    SELECT
     @SQL = N'SELECT @max = MAX(DATALENGTH(' + QUOTENAME(@Col) + '))
            FROM  ' + QUOTENAME(@schema) + '.' + QUOTENAME(@TableName); --schema!

    In your example, if the Person tables would have the same (N)VARCHAR-fields, and there is one table with the dbo-schema, the script would not generate any errors. The results however would not be what you expected.

  • Luis Cazares

    SSC Guru

    Points: 183532

    Darko Martinovic - Thursday, July 6, 2017 12:50 AM

    Thank's the author for this question.
    In the real world, this script would end with an error. The reason is trivial. There is no scheme in the script.
    Eg. I look at a database model and in that model a table called 'Person'. There are 10 tables with that name, all in different schemas.  In that case the script would produce an error.
    But including the schema, everything works fine.

    DECLARE @schema SYSNAME= 'TheSchema', --Include schema
    ---

      SELECT
       COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_Name = @TableName
       AND TABLE_SCHEMA = @schema -- there is no schema!
       AND DATA_TYPE LIKE '%varchar';
    ---
    SELECT
     @SQL = N'SELECT @max = MAX(DATALENGTH(' + QUOTENAME(@Col) + '))
            FROM  ' + QUOTENAME(@schema) + '.' + QUOTENAME(@TableName); --schema!

    And here I was thinking the problem was with the UPDATE statement as it didn't include the table name.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Darko Martinović

    Hall of Fame

    Points: 3535

    Mighty - Thursday, July 6, 2017 6:35 AM

    Darko Martinovic - Thursday, July 6, 2017 12:50 AM

    Thank's the author for this question.
    In the real world, this script would end with an error. The reason is trivial. There is no scheme in the script.
    Eg. I look at a database model and in that model a table called 'Person'. There are 10 tables with that name, all in different schemas.  In that case the script would produce an error.
    But including the schema, everything works fine.

    DECLARE @schema SYSNAME= 'TheSchema', --Include schema
    ---

      SELECT
       COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_Name = @TableName
       AND TABLE_SCHEMA = @schema -- there is no schema!
       AND DATA_TYPE LIKE '%varchar';
    ---
    SELECT
     @SQL = N'SELECT @max = MAX(DATALENGTH(' + QUOTENAME(@Col) + '))
            FROM  ' + QUOTENAME(@schema) + '.' + QUOTENAME(@TableName); --schema!

    In your example, if the Person tables would have the same (N)VARCHAR-fields, and there is one table with the dbo-schema, the script would not generate any errors. The results however would not be what you expected.

    Yeah, right.
    I did not want to write this because, as the author says, we turned a bit off the subject.
    In any case, it is a good practice to use the scheme.
    Thank you for your replay. All glory goes to the author. 🙂

  • x

    SSC-Insane

    Points: 23352

    lol I made a wild guess because "TLDR" wasn't a choice, easiest 2 points I ever got!

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71320

    Luis Cazares - Thursday, July 6, 2017 6:39 AM

    And here I was thinking the problem was with the UPDATE statement as it didn't include the table name.

    did i miss something?
       UPDATE #Cols SET MaxLen = @max  WHERE ColumnName = @Col;

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Luis Cazares

    SSC Guru

    Points: 183532

    Stewart "Arturius" Campbell - Thursday, July 6, 2017 7:20 AM

    Luis Cazares - Thursday, July 6, 2017 6:39 AM

    And here I was thinking the problem was with the UPDATE statement as it didn't include the table name.

    did i miss something?
       UPDATE #Cols SET MaxLen = @max  WHERE ColumnName = @Col;

    First of all, thank you for the question. I don't mean to be annoying.
    The issue is similar to the schema problem. A column name can be the same for columns on different tables. Some people put a Description or Name column on every table. I would say that table (and schema) should be part of your table definition to fully identify the column.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • sestell1

    SSChampion

    Points: 10230

    How did you get away without equating your @max variable within your query executed by sp_executesql with the @max variable in your code that calls it?

    Does specifying "@Max OUTPUT" after the parameter definition assume the two are equated since there is only one output variable?

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Yes, if the target table existed in multiple schemas then you would have multiple column names returned in the cursor.  I all the tables in the multiple schemas were identical, you would not have an issue, just that the query would run multiple times over the duplicate column names in the target table.  If they were different, then you would have a problem.  The target table also would be in the default schema for the user running the code.

    I have written a lot of dynamic SQL and this was a fairly easy question to answer.  What I was looking for was perhaps a missing OUTPUT, either in the definition of the parameter string or in the invokatiion of the sp_executesql itself.

    I agree, one should always specify the schema of the table.  Also, I wouldn't use INFORMATION_SCHEMA views, I would use the system views themselves (sys.tables, sys.columns, etc.).

  • Lynn Pettis

    SSC Guru

    Points: 442118

    I think I did find an oops looking at it again:
    EXEC sp_executesql @SQL, N'@Max INT OUTPUT', @Max OUTPUT;

    Should be, iirc, be:
    EXEC sp_executesql @SQL, N'@Max INT OUTPUT', @Max = @max OUTPUT;

    Yes, you can use the same variable name, just looks really funny.

  • Carlo Romagnano

    SSC-Insane

    Points: 21713

    Here a cursor free version:
    DECLARE @TableName SYSNAME = 'MyTable',@Schema SYSNAME = 'dbo', @SQL NVARCHAR(MAX) = '', @max INT
    ,@UnionALL NVARCHAR(11)= '';

    SELECT @SQL += @UnionAll + N'SELECT ''' + COLUMN_NAME + ''',MAX(DATALENGTH(' + QUOTENAME(COLUMN_NAME) + ')) FROM ' + QUOTENAME(@TableName)
      ,@UnionALL = ' UNION ALL '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    AND TABLE_SCHEMA = @Schema
    AND DATA_TYPE LIKE '%varchar';

      EXEC sp_executesql @SQL

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

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