Stored Procedure with dynamic pivot doesn't return results because of mismatched date-formats

Question

I have a view with a pivot function in it:

SELECT     TOP (10000) PivotTable.BEZEICHNUNG, PivotTable.[29.01.2013] AS ...
FROM (SELECT l.LONGNAME AS BEZEICHNUNG, m.WASSPNN AS STAND, m.SMPDATE AS DATUM
      FROM ... 
      WHERE l.PRJ_ID = '86WAA5') AS SourceTable PIVOT (AVG(STAND) FOR DATUM IN ([29.01.2013], [27.02.2013], [27.03.2013], [24.04.2013], ...)) AS PivotTable
WHERE ...
ORDER BY BEZEICHNUNG

This works just fine and displays all the hardcoded columns. Now I need to refactor this, so it takes dynamic column values depending on a Start and End date. I used this SO answer as a guideline

Heres the code to the procedure in question:

BEGIN
    DECLARE @PivotQuery NVARCHAR(MAX)
    DECLARE @cols AS NVARCHAR(MAX)
    DECLARE @ProjectID AS NVARCHAR(15)

    SET NOCOUNT ON

    SET @ProjectID = '86WAA5'
    SET @PivotQuery = 'SELECT TOP (10000) BEZEICHNUNG,'+@cols+'
                       FROM (SELECT  l.LONGNAME AS BEZEICHNUNG, m.WASSPNN AS STAND, m.SMPDATE AS DATUM
                             FROM  ...
                             WHERE l.PRJ_ID = ' + @ProjectID + ') AS SourceTable 
                       PIVOT (AVG(STAND) FOR DATUM IN ('+ @cols + ')) AS PivotTable         
                       ORDER BY BEZEICHNUNG
     SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(m.SMPDATE) 
                FROM ...
                WHERE l.PRJ_ID = '86WAA5' AND
                m.SMPDATE BETWEEN @StartDate AND @EndDate
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

     EXECUTE(@PivotQuery)
END
GO

As you can see, the PivotQuery is a string variable, which I feed with the results from the @cols variable.

The field SMPDATE is of type DateTime. If I I select it plainly it does get returned formatted as: 2010-01-01 00:00:00.000 If I run the SELECT STUFF(...) query on its own, the returned date variables are formatted as: [Jan 01, 2012 12:00AM],[Jan 19, 2012 12:00AM],[...

I have the suspicion that this format mismatch is causing the stored procedure to return no values. How should I build this procedure properly to avoid this problems?

EDIT: Bluefeet has solved the problem. If somebody stumbles above this and wants to reuse this piece of code: The @PivotQuery variable is a string. As such the quotationmarks need to be added and escaped before inserting @ProjectId into it. The snippet would then look like this:

WHERE l.PRJ_ID = ''' + @ProjectID + ''') AS SourceTable

Without this, the project Id would be treated as an integer value, which in most cases is fine. But if you deal with Id like mine here, you need to treat this as a string.

Run the dynamic sql code outside of the stored proc, you need to see the sql string that is being generated. — Taryn, Oct 27 '14 at 11:28
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/63700/discussion-between-serv-and-bluefeet). — Marco, Oct 27 '14 at 11:34
For one, that's not a date, it's a string. For two, when you have that output and it's less obvious (say, September 7th), is your entire audience going to understand that's d.m.y and not m.d.y? We have standards for a reason, and that's not one of them. — Aaron Bertrand, Oct 28 '14 at 00:42
Standards have their reason, ofc. But if the engineer says, he wants that report in a German date format, then the engineer gets his date format. In fact, the whole system uses the German date format "D.M.Y". I don't see anything wrong with being awful in this case. — Marco, Oct 28 '14 at 07:19

score 2 · Accepted Answer · edited Jan 18 '21 at 12:34

Based on our discussion in chat the problem is because you are generating the list of columns after your query string. Basically you don't have any columns being used in the final query so you are returning nothing.

You'll need to alter the order of your execution:

BEGIN
DECLARE @PivotQuery NVARCHAR(MAX)
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @ProjectID AS NVARCHAR(15)

SET NOCOUNT ON

SET @ProjectID = '86WAA5'

 SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(m.SMPDATE) 
            FROM ...
            WHERE l.PRJ_ID = '86WAA5' AND
            m.SMPDATE BETWEEN @StartDate AND @EndDate
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @PivotQuery = 'SELECT TOP (10000) BEZEICHNUNG,'+@cols+'
                   FROM (SELECT  l.LONGNAME AS BEZEICHNUNG, m.WASSPNN AS STAND, m.SMPDATE AS DATUM
                         FROM  ...
                         WHERE l.PRJ_ID = ' + @ProjectID + ') AS SourceTable 
                   PIVOT (AVG(STAND) FOR DATUM IN ('+ @cols + ')) AS PivotTable         
                   ORDER BY BEZEICHNUNG

 EXECUTE(@PivotQuery)
END
GO

'Based on our discussion' this is totally the correct answer. I have edited my question to address another problem in my sql code, in case somebody wants to reuse it. — Marco, Oct 27 '14 at 13:11

Stored Procedure with dynamic pivot doesn't return results because of mismatched date-formats

1 Answers1