SQL Invalid Column error on Dynamic Query

Question

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?

select [2014-01-24] as test? you have fields named with dates? — Marc B
– Marc B, Commented Jan 25, 2016 at 16:46
Yes, each column will have a date and then data for those dates. Is there something wrong with that? — user41829
– user41829, Commented Jan 25, 2016 at 16:49
well, it's one thing to produce a pivot table where those columns are built on-the-fly. it's another to have an actual table with those field names. dynamic field names are almost never a good idea. — Marc B
– Marc B, Commented Jan 25, 2016 at 16:53

SQLChao · Accepted Answer · 2016-01-25 16:55:01Z

2

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.

answered Jan 25, 2016 at 16:55

SQLChao

7,8951 gold badge19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user41829 Over a year ago

Thanks this at least helped me visualize what was going on I didn't know you could do this, thanks!

SQLChao Over a year ago

@user41829 No problem. It will definitely help when you need to figure out what a variable is getting set too when doing dynamic sql. I've had very large queries where I put a bunch of checkpoints that would just print what the variables currently were.

RoMEoMusTDiE Over a year ago

Other option would be using coalesce

Collectives™ on Stack Overflow

SQL Invalid Column error on Dynamic Query

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related