0

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?

user41829
  • 95
  • 1
  • 1
  • 10
  • `select [2014-01-24] as test`? you have fields named with dates? – Marc B Jan 25 '16 at 16:46
  • Yes, each column will have a date and then data for those dates. Is there something wrong with that? – user41829 Jan 25 '16 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 Jan 25 '16 at 16:53

1 Answers1

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.

SQLChao
  • 7,709
  • 1
  • 17
  • 32
  • Thanks this at least helped me visualize what was going on I didn't know you could do this, thanks! – user41829 Jan 25 '16 at 17:02
  • @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. – SQLChao Jan 25 '16 at 17:04
  • Other option would be using coalesce – RoMEoMusTDiE Jan 25 '16 at 17:49