CODEWITHSUNDEEP
sqlsql-server
FebWind
FebWind

Reputation: 423

why does this Dynamic Sql Statement fail?

This is no any problem:

select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE 
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='employee';

But, when it moved to a dynamic statement, it always fail:

begin
DECLARE @sqlstatement VARCHAR(MAX);
DECLARE @TableName varchar(max)='employee';

set @sqlstatement='select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='+@TableName;
print (@sqlstatement);
EXECUTE(@sqlstatement);

end

error says:Invalid column name 'employee'

Upvotes: 2

Views: 198

Answers (2)

Devart
Devart

Reputation: 122032

Using sys.sp_executesql more desirable to executing dynamic SQL, so try this one -

DECLARE @SQL NVARCHAR(MAX);
DECLARE @TableName SYSNAME = 'Employee';

SELECT @SQL = '
USE [AdventureWorks2012];
SELECT 
      COLUMN_NAME = c.name
    , DATA_TYPE = TYPE_NAME(c.user_type_id)
    , CHARACTER_MAXIMUM_LENGTH = c.max_length
    , IS_NULLABLE = c.is_nullable
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
WHERE o.name = ''' + @TableName + '''';

PRINT @SQL;
EXEC sys.sp_executesql @SQL;

Upvotes: 2

John Woo
John Woo

Reputation: 263893

you should wrap it with single quotes since column table_name is string,

set @sqlstatement='select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
from [AdventureWorks2012].INFORMATION_SCHEMA.COLUMNS WHERE table_name='''+@TableName+'''';

Upvotes: 9

Related Questions