Passing date restraints in a SQL Pivot table Result

Question

This procedure works until I try to pass parameters to limit the records selected in the primary select query of the pivot table procedure.

If I comment out references to @Startdate and @EndDate, it works but is selecting all records. What changes need to be made so it will prompt the date range variables and incorporate them into the query results?

The error I get is: Msg 137, Level 15, State 2, Line 10 Must declare the scalar variable "@StartDate".

Here's the proc below.

Thanks in advance.

ALTER PROCEDURE usp_Get_Monthly_IR_Report

@StartDate smalldatetime , @EndDate smalldatetime AS

DECLARE @ColumnNames NVARCHAR(MAX) = ''

DECLARE @SQL NVARCHAR(Max) = ''

SELECT @ColumnNames += QUOTENAME(GL_Facilities_Name) +',' FROM GL_Facilities

SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames)-1) SET @sql =

'SELECT * FROM ( SELECT IR_Priority_level.IR_Priority_Level_Text as [Priority], IR_Types.IR_Types_Text AS [Type], GL_Facilities.GL_Facilities_Name FROM IR_Types LEFT JOIN IR_Priority_Level ON IR_Types.IR_Priority_Level_ID = IR_Priority_Level.IR_Priority_Level_ID LEFT OUTER JOIN IR_Incidents ON IR_Types.IR_Types_ID = IR_Incidents.IR_Types_ID LEFT JOIN GL_Business_Line_Contracts ON IR_Incidents.GL_Business_Line_Contracts_ID = GL_Business_Line_Contracts.GL_Business_Line_Contracts_ID LEFT JOIN GL_Facilities ON GL_Business_Line_Contracts.GL_Facilities_ID = GL_Facilities.GL_Facilities_ID WHERE IR_Incidents_Date BETWEEN @StartDate and @EndDate ) AS BaseData

PIVOT ( Count(GL_Facilities_Name) FOR GL_Facilities_Name IN (' +@ColumnNames + ') ) AS PivotTable'

EXEC sp_executesql @SQL

Does this help? http://stackoverflow.com/questions/1036745/t-sql-how-to-use-parameters-in-dynamic-sql — Liesel, Mar 15 '16 at 03:06

score 0 · Answer 1 · answered Mar 18 '16 at 18:32

since you're trying to use the procedure parameters in your dynamic query, you need to pass them in to the dynamic query..

ALTER PROCEDURE usp_Get_Monthly_IR_Report
    @StartDate SMALLDATETIME,
    @EndDate SMALLDATETIME
AS 
BEGIN
    DECLARE @ColumnNames NVARCHAR(MAX) = ''
    DECLARE @SQL NVARCHAR(MAX) = ''

    -- define params that will be used in the dynamic query
    DECLARE @ParmDefinitions NVARCHAR(MAX) = '@StartDate SMALLDATETIME, @EndDate SMALLDATETIME'

    SELECT  @ColumnNames += QUOTENAME(GL_Facilities_Name) + ','
    FROM    GL_Facilities

    SET @ColumnNames = LEFT(@ColumnNames,LEN(@ColumnNames) - 1)
    SET @sql = 'SELECT * FROM ( 
                    SELECT  IR_Priority_level.IR_Priority_Level_Text as [Priority], 
                            IR_Types.IR_Types_Text AS [Type], 
                            GL_Facilities.GL_Facilities_Name 
                    FROM    IR_Types 
                            LEFT JOIN IR_Priority_Level ON IR_Types.IR_Priority_Level_ID = IR_Priority_Level.IR_Priority_Level_ID 
                            LEFT OUTER JOIN IR_Incidents ON IR_Types.IR_Types_ID = IR_Incidents.IR_Types_ID 
                            LEFT JOIN GL_Business_Line_Contracts ON IR_Incidents.GL_Business_Line_Contracts_ID = GL_Business_Line_Contracts.GL_Business_Line_Contracts_ID 
                            LEFT JOIN GL_Facilities ON GL_Business_Line_Contracts.GL_Facilities_ID = GL_Facilities.GL_Facilities_ID 
                    WHERE   IR_Incidents_Date BETWEEN @StartDate and @EndDate 
                ) AS BaseData
                PIVOT ( 
                    Count(GL_Facilities_Name) FOR GL_Facilities_Name IN (' + @ColumnNames + ') 
                ) AS PivotTable'

    EXEC sp_executesql @SQL, @ParmDefinitions, @StartDate = @StartDate, @EndDate = @EndDate
END

Passing date restraints in a SQL Pivot table Result

1 Answers1