Invalid column name when using dynamic SQL

Question

I want to execute the following T-SQL dynamic statement:

CREATE PROCEDURE MergeTable @TableName NVARCHAR(max) 
AS BEGIN
DECLARE @MergeStatement NVARCHAR(max)
SET @MergeStatement = 'SELECT Query FROM dbo.QueryMergeDWH WHERE SourceTableName = ' + @TableName
EXEC sp_executesql @MergeStatement
END

EXEC MergeTable @TableName = 'SGPREINVOICE'

However, this gives me the following error:

Msg 207, Level 16, State 1, Line 17 Invalid column name 'SGPREINVOICE'.

This actually works:

SELECT 'SELECT Query FROM dbo.QueryMergeDWH WHERE SourceTableName = ' + 'SGPREINVOICE'

What am I doing wrong here?

WARNING: This is a huge injection issue. You are giving a malicious person the ability to inject 2 billion bytes (1 billion characters) of whatever SQL they want. If this is executed under an LOGIN/USER with high enough permissions they could literally do anything they wanted with minimal effort. NEVER inject unsanitised strings into a dynamic statement and don't use MAX for an object's name. There is a specific data type for object names: sysname. — Thom A
– Thom A ♦, Commented May 27, 2021 at 10:28
Here's an parameterized query example that will address your problem and improve security: SET @MergeStatement = N'SELECT Query FROM dbo.QueryMergeDWH WHERE SourceTableName = @TableName';EXEC sp_executesql @MergeStatement, N'@TableName nvarchar(523)', @TableName = @TableName;; — Dan Guzman
– Dan Guzman, Commented May 27, 2021 at 10:43

Charlieface · Accepted Answer · 2021-05-27 11:17:07Z

You need to parameterize you dynamic query. So you pass @TableName all the way through

CREATE PROCEDURE MergeTable @TableName NVARCHAR(max) 
AS

DECLARE @MergeStatement NVARCHAR(max);

SET @MergeStatement = '
SELECT Query
FROM dbo.QueryMergeDWH
WHERE SourceTableName = @TableName;
';

EXEC sp_executesql
    @MergeStatement,
    N'@TableName nvarchar(max)',
    @TableName = @TableName;

GO

But it's unclear what's dynamic about that, you could just as well do


CREATE PROCEDURE MergeTable @TableName NVARCHAR(max) 
AS

SELECT Query
FROM dbo.QueryMergeDWH
WHERE SourceTableName = @TableName;

GO

Collectives™ on Stack Overflow

Invalid column name when using dynamic SQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related