CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Luuk van Gasteren
Luuk van Gasteren

Reputation: 111

Invalid column name when using dynamic SQL

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?

Upvotes: 1

Views: 691

Answers (1)

Charlieface
Charlieface

Reputation: 71159

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

Upvotes: 1

Related Questions