Dynamic SQL

Question

Post reply

Dynamic SQL

Stewart "Arturius" Campbell

SSC Guru

Points: 72622
More actions
July 5, 2017 at 11:26 pm

#324378

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”

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

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

HappyGeek SSCoach Points: 18729 More actions · Answer 1

HappyGeek

SSCoach

Points: 18729

July 6, 2017 at 12:12 am

#1949617

Good question thanks Stewart.

...

Darko Martinović Hall of Fame Points: 3545 More actions · Answer 2

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-2 = MAX(DATALENGTH(' + QUOTENAME(@Col) + '))
        FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@TableName); --schema!

Stewart "Arturius" Campbell SSC Guru Points: 72622 More actions · Answer 3

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-2 = 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: 3545 More actions · Answer 4

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-2 = 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: 9063 More actions · Answer 5

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-2 = 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: 183706 More actions · Answer 6

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-2 = 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: 3545 More actions · Answer 7

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-2 = 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: 23660 More actions · Answer 8

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

Stewart "Arturius" Campbell SSC Guru Points: 72622 More actions · Answer 9

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-2 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: 183706 More actions · Answer 10

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-2 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 More actions · Answer 11

How did you get away without equating your @max-2 variable within your query executed by sp_executesql with the @max-2 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: 442467 More actions · Answer 12

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: 442467 More actions · Answer 13

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-2 OUTPUT;

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

Carlo Romagnano SSC-Insane Points: 22666 More actions · Answer 14

Here a cursor free version:
DECLARE @TableName SYSNAME = 'MyTable',@Schema SYSNAME = 'dbo', @SQL NVARCHAR(MAX) = '', @max-2 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