尝试在使用 DECLARE 语句时从 asp.net 传递参数，但收到错误消息"必须声明标量变量"@pID"

Question

我有一个名为ReportValues的表，它将玩家的所有信息存储在键值对中。该表具有来自另一个名为Reports的表的外键。我试图将每个报告的所有数据放在一行中，其中键将是列名，值将填充返回的表。如果我手动插入@pID而不是作为参数，那么代码可以工作，但当使用参数时，我会得到以下错误："必须声明标量变量"@pID"。这是我的代码：

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

"和RV2.value=@pID"是动态字符串的一部分，在动态字符串构建查询中不被视为变量。在SQL Server中，变量作用域仅扩展到当前执行的批处理或函数，因此在"EXEC（）"内执行的动态构建代码不知道变量@pID
你可以用几种方法来解决这个问题，其中一种方法如下：

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 += "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 ";

（注意'+@pID+'）

另一种（我认为是更好的方法）是取代：

"EXEC(@DynamicPIVOT) "

带有

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

注意：@DynamicPIVOT必须声明为NVARCHAR（MAX）
注意2:声明@pID为与RV2.keyName相同的数据类型。这将避免在查询执行期间进行类型转换

为了帮助将来诊断类似的问题，我建议您在执行sqlQuery之前检查它的值，并将其粘贴到SQL Management studio中。这将明确哪些部分是字符串，哪些部分是SQL可执行代码。