Dynamic SQL Select variable name

Question

I have the following SQL statement, which works fine:

DECLARE @TableName VARCHAR(250);
SET @TableName = 'TBL_Name'
SELECT QUOTENAME(@TableName) As TableName, HASHBYTES('SHA1', (SELECT * FROM TBL_Name FOR XML RAW)) As TableHash

I now want to be able to pass in the table name as a variable, so I can use this as a function, so I tried this:

DECLARE @TableName VARCHAR(250);
SET @TableName = 'TBL_Name'
EXEC('SELECT QUOTENAME(' + @TableName + ') As TableName, HASHBYTES(''SHA1'', (SELECT * FROM TBL_NameFOR XML RAW)) As TableHash');

When I run this I get the following error:

Invalid column name 'TBL_Name'.

If I remove the column name and change it to this, it works:

DECLARE @TableName VARCHAR(250);
SET @TableName = 'TBL_Name'
EXEC('SELECT HASHBYTES(''SHA1'', (SELECT * FROM ' + @TableName + ' FOR XML RAW)) As TableHash');

MY question is, how can I get the query to output the table name as a column?

QUOTENAME(' + @TableName + ', '''')

Daniel Brughera
– Daniel Brughera

2019-03-27 10:30:56 +00:00
Commented Mar 27, 2019 at 10:30 — Daniel Brughera
– Daniel Brughera, Commented Mar 27, 2019 at 10:30
@DanielBrughera that did not work - can you elaborate?

Alex
– Alex

2019-03-27 10:33:33 +00:00
Commented Mar 27, 2019 at 10:33 — Alex
– Alex, Commented Mar 27, 2019 at 10:33

Daniel Brughera · Accepted Answer · 2019-03-27 10:57:25Z

1

QUOTENAME function has a second optional parameter to wrap the value with a different character, in your case you need a single quote to use the @TableName as varchar value in the first use and as a table name in the second one

DECLARE @TableName NVARCHAR(250);
SET @TableName = N'TBL_Name'
DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + QUOTENAME(@TableName,'''') + N' as TableName, HASHBYTES(''SHA1'', (SELECT * FROM ' + QUOTENAME(@TableName) + N' FOR XML RAW)) As TableHash'
EXEC(@SQL);

It will create the following code

SELECT 'TBL_Name' as TableName, HASHBYTES('SHA1', (SELECT * FROM [TBL_Name] FOR XML RAW)) As TableHash

edited Mar 27, 2019 at 10:57

answered Mar 27, 2019 at 10:38

Daniel Brughera

1,6511 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ilyes Over a year ago

I would use NVARCHAR(MAX) instead of VARCHAR(MAX)

Daniel Brughera Over a year ago

It doesn't make difference for this specific case, but right, is the best option writing dynamic SQL

Collectives™ on Stack Overflow

Dynamic SQL Select variable name

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related