Пытаюсь передать параметры из asp.net при использовании инструкции DECLARE, но получаю сообщение об ошибке: «Необходимо объявить скалярную переменную @pID»

У меня есть таблица 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;

}

sql-server asp.net tsql scalar sqlparameter
person Orensig    schedule 15.05.2016    source источник
comment
Я не думаю, что вы можете передавать переменные в exec таким образом. Вместо этого вы должны попробовать sp_executesql   -  person James Z    schedule 15.05.2016
comment
Эта проблема не имеет ничего общего с sp_executesql   -  person Alex    schedule 15.05.2016

Ответы (1)


arrow_upward
1
arrow_downward

"and 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 ";

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

(обратите внимание на ' + @pID + ')

Другой (я бы сказал, лучший способ) - заменить:

"EXEC(@DynamicPIVOT) "

с

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

Примечание. @DynamicPIVOT должен быть объявлен как NVARCHAR( MAX )
Примечание 2: объявить @pID тем же типом данных, что и RV2.keyName. Это позволит избежать преобразования типов во время выполнения запроса.

Чтобы помочь в диагностике подобных проблем в будущем, я предлагаю вам проверить значение sqlQuery непосредственно перед его выполнением и вставить его в студию управления SQL. Это прояснит, какие части являются строками, а какие — исполняемым кодом SQL.

person Alex    schedule 15.05.2016
comment
Я пробовал это, но это не сработало. Вместо этого я создал хранимую процедуру, и она работает. Есть ли риск так делать? - person Orensig; 16.05.2016
comment
Если вы имеете в виду создание процедуры, то нет, никакого риска. - person Alex; 17.05.2016
comment
Было исключение, говорящее, что что-то не так в '(.' в моем запросе. - person Orensig; 17.05.2016
comment
Если ваша ошибка возникает при использовании моего первого решения, вам может потребоваться преобразовать @pID в varchar, например. CAST( @pID AS VARCHAR ). Я также обновил свой ответ - person Alex; 18.05.2016