Get result from select inside stored procedure

Question

I have a stored procedure in Microsoft SQL Server that inserts data into a table and returns as output an error code and an error description.

private string[] SetServiceUpdateData(XmlDocument xmlDoc)
{
    string[] returnParms = new string[2];

    SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["Acessos"].ToString());

    try
    {
        using (SqlCommand cmd = new SqlCommand("dbo.sp_ServiceUpdateData", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Id", SqlDbType.VarChar, 255).Value = ValidateParameter(xmlDoc.GetElementsByTagName("Id"));
            cmd.Parameters.Add("@Status", SqlDbType.VarChar, 255).Value = ValidateParameter(xmlDoc.GetElementsByTagName("Status"));

            cmd.Parameters.Add("@ERROR_CODE", SqlDbType.TinyInt, 4).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@ERROR_DESCRIPTION", SqlDbType.VarChar, 255).Direction = ParameterDirection.Output;

            conn.Open();
            cmd.ExecuteNonQuery();

            dataReader.Close();

            returnParms[0] = Convert.ToString(cmd.Parameters["@Error_code"].Value);
            returnParms[1] = Convert.ToString(cmd.Parameters["@ERROR_DESCRIPTION"].Value);
        }
    }
    catch (Exception e)
    {
        returnParms[0] = returnParms[0] + e.Source;
        returnParms[1] = returnParms[1] + e.Message;
    }
    finally
    {
        if ((conn != null) && (conn.State == ConnectionState.Open))
        {
            conn.Close();
            conn.Dispose();
            conn = null;
        }
    }
    return returnParms;
}

The stored procedure is something like this (it has a lot of params so I deleted a lot of them):

@Id varchar(255) = NULL,
@Status varchar(255) = NULL,        
@ERROR_CODE TINYINT = NULL OUTPUT,
@ERROR_DESCRIPTION VARCHAR(255) = NULL OUTPUT

BEGIN
    UPDATE TABLE 
    SET Status = ISNULL(@Status, Status) 
    WHERE Id = @Id

    SELECT Status 
    FROM TABLE 
    WHERE Id = @Id
END

The code and the stored procedure work fine, I can Insert values and the outputs are returned. The stored procedure also has a select after the insertion the I need to return to json.

How do I return the value of that select at the same time as returning the return params from the stored procedure?

Use ExecuteReadinstead of ExecuteNonQuery then load the reader into a datatable https://stackoverflow.com/questions/13870843/turning-a-sqlcommand-with-parameters-into-a-datatable — B. Lec, Jan 31 '20 at 12:55
https://stackoverflow.com/questions/32052621/possible-to-return-an-out-parameter-with-a-datareader — B. Lec, Jan 31 '20 at 12:56
You need to assign the values to return to your output variables inside your SP — Misery, Jan 31 '20 at 13:24

score 0 · Answer 1 · answered Feb 01 '20 at 12:38

For that you need to use dataset to execute stroredprocedure. i have modified your code below.

private string[] SetServiceUpdateData(XmlDocument xmlDoc)
{
    string[] returnParms = new string[2];

    SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["Acessos"].ToString());

    try
    {
        using (SqlCommand cmd = new SqlCommand("dbo.sp_ServiceUpdateData", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Id", SqlDbType.VarChar, 255).Value = ValidateParameter(xmlDoc.GetElementsByTagName("Id"));
            cmd.Parameters.Add("@Status", SqlDbType.VarChar, 255).Value = ValidateParameter(xmlDoc.GetElementsByTagName("Status"));

            cmd.Parameters.Add("@ERROR_CODE", SqlDbType.TinyInt, 4).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@ERROR_DESCRIPTION", SqlDbType.VarChar, 255).Direction = ParameterDirection.Output;
  DataSet ds = new DataSet("MyDataSet");
            conn.Open();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = sqlComm;

        da.Fill(ds);

            dataReader.Close();

            returnParms[0] = Convert.ToString(cmd.Parameters["@Error_code"].Value);
            returnParms[1] = Convert.ToString(cmd.Parameters["@ERROR_DESCRIPTION"].Value);
        }
    }
    catch (Exception e)
    {
        returnParms[0] = returnParms[0] + e.Source;
        returnParms[1] = returnParms[1] + e.Message;
    }
    finally
    {
        if ((conn != null) && (conn.State == ConnectionState.Open))
        {
            conn.Close();
            conn.Dispose();
            conn = null;
        }
    }
    return returnParms;
}

score 0 · Answer 2 · edited Feb 03 '20 at 11:39

0

Thank you for all the replies.

The solution I used is this one:

SqlDataReader dataReader = cmd.ExecuteReader();

DataTable dataTable = new DataTable();
dataTable.Load(dataReader);

returnParms[3] = string.Empty;
returnParms[3] = JsonConvert.SerializeObject(dataTable);

Using Newtonsoft to convert to json and I had some errors on my SP, ints that were varchars.

Thank you all for the help

edited Feb 03 '20 at 11:39

Juan Salvador Portugal

1,233
4
20
38

answered Feb 03 '20 at 11:14

Jules

11
1

Get result from select inside stored procedure

2 Answers2