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?
Advertisement
Answer
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