CODEWITHSUNDEEP
sql-serverdynamicquery
user41829
user41829

Reputation: 105

SQL Invalid Column error on Dynamic Query

I have the following query:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(MaxDate)
FROM (SELECT DISTINCT MaxDate FROM CustPeriodTable) AS MaxDates

SET @DynamicPivotQuery =

N'SELECT ' + @ColumnName + ' AS Test, min(rn)
FROM CustPeriodTable '

EXEC sp_executesql @DynamicPivotQuery

I'm getting an invalid column name error for every entry in @ColumnName. I'm in the process of setting up a more complicated query that will involve a pivot but I'm trying to get this chunk working first. Can anyone point out where this problem might be coming from?

Upvotes: 0

Views: 638

Answers (1)

SQLChao
SQLChao

Reputation: 7837

For these types of issues you need to look at what the dynamic query is. Instead of executing it just select it.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(MaxDate)
FROM (SELECT DISTINCT MaxDate FROM CustPeriodTable) AS MaxDates

SET @DynamicPivotQuery =

N'SELECT ' + @ColumnName + ' AS Test, min(rn)
FROM CustPeriodTable '

SELECT @DynamicPivotQuery

-- EXEC sp_executesql @DynamicPivotQuery

From here you should be able to determine what the issue is.

Upvotes: 2

Related Questions