Trying to pass parameters from asp.net when using a DECLARE statement but getting an error saying "Must declare the scalar variable "@pID"

Question

I have a table called ReportValues that stores all the information about a player in key and value pairs. The table has a foreign key from another table called Reports. I'm trying to get all the data in one row for each report where the keys will be the column names and the values will populate the returned table. If I'm inserting the @pID manually and not as a parameter, then the code works, but when using parameter I get the following error: "Must declare the scalar variable "@pID". Here is my code:

public DataTable getBarChartData(int p_ID)        
{
    //this query is transposing rows into columns as each key gets a column and the grouping variable is RV.report
    string sqlQuery = "DECLARE @keyName AS VARCHAR(MAX) ";
    sqlQuery += "SELECT @keyName = ";
    sqlQuery += "COALESCE(@keyName + ', ', '') + CAST(keyName AS VARCHAR(20)) ";
    sqlQuery += "FROM(SELECT DISTINCT keyname FROM ReportValues) ActivePlayers ";
    sqlQuery += "SELECT @keyName keyNames ";
    sqlQuery += "DECLARE @DynamicPIVOT AS VARCHAR(MAX) ";
    sqlQuery += "SELECT @DynamicPIVOT = 'SELECT ' + @keyName + ";
    sqlQuery += "' FROM ( ";
    sqlQuery += "SELECT RV.report, RV.keyname, RV.value FROM ReportValues RV ";
    sqlQuery += "join ReportValues RV1 on RV.report = RV1.report ";
    sqlQuery += "join ReportValues RV2 on RV.report = RV2.report ";
    sqlQuery += "where RV1.value <> 1 and RV1.keyName = ''SessionStage'' and RV2.value =  @pID and RV2.keyName = ''PId'' and RV.report in ( ";
    sqlQuery += "SELECT id FROM Reports r ";
    sqlQuery += "WHERE r.timeStamp >= dateadd(hour, -240, GETDATE()) ";
    sqlQuery += ") ";
    sqlQuery += ") ActivePlayers";
    sqlQuery += "PIVOT(";
    sqlQuery += "MAX(value) FOR keyname IN(' + @keyName + ') ";
    sqlQuery += ") Result; ' ";
    sqlQuery += "EXEC(@DynamicPIVOT) ";
    int pID = p_ID;
    //this passes the query and param to an execution function
    DataTable dtLabels = GetBarChartDataByUser(sqlQuery, pID);
    return dtLabels;
}

//this is the sql execution function
public DataTable GetBarChartDataByUser(string strQuery, int pID)
{
    SqlConnection con = new SqlConnection(createConnectionReadOnly());
    con.Open();
    try
    {

        SqlCommand cmd = con.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strQuery;
        cmd.Parameters.AddWithValue("@pID", pID);
        SqlDataAdapter dap = new SqlDataAdapter(cmd);
        //here is where I get the error
        DataSet ds = new DataSet();
        dap.Fill(ds);
        return ds.Tables[0];
    }
    catch (Exception ex)
    {

    }
    finally
    {
        if (con.State == ConnectionState.Open)
        {
            con.Close();
        }
    }
    return null;

}

Answer 1

"and RV2.value = @pID" is part of a dynamic string and is not treated as a variable in your dynamic string building query. In SQL Server variable scope only extends to currently executing batch or function and therefore the dynamicly built code executing inside "EXEC()" has no knowledge of variable @pID.
You can solve the problem in a few ways, one of them is below:

sqlQuery += "join ReportValues RV2 on RV.report = RV2.report ";
sqlQuery += "where RV1.value <> 1 and RV1.keyName = ''SessionStage'' and RV2.value =  @pID and RV2.keyName = ''PId'' and RV.report in ( ";
sqlQuery += "SELECT id FROM Reports r ";

to

sqlQuery += "join ReportValues RV2 on RV.report = RV2.report ";
sqlQuery += "where RV1.value <> 1 and RV1.keyName = ''SessionStage'' and RV2.value =  ' + CONVERT( VARCHAR, @pID ) + ' and RV2.keyName = ''PId'' and RV.report in ( ";
sqlQuery += "SELECT id FROM Reports r ";

(notice ' + @pID + ')

Another (I would argue a better way) is to replace:

"EXEC(@DynamicPIVOT) "

with

"EXEC sp_executesql @DynamicPIVOT, N'@pID INT', @pID "

Note: @DynamicPIVOT must be declared as NVARCHAR( MAX )
Note 2: declare @pID to be the same data type as RV2.keyName. This will avoid type conversions during query execution

To help with diagnosing similar problems in the future, I suggest you inspect the value of sqlQuery just before it is executed and paste it in SQL Management studio. This will make it clear which parts are strings and which parts are SQL executable code.