Display result of a sql server stored procedure in asp.net c#

Question

I have a stored procedure and the result I wanted displayed on a page. The problem is that the result will not be stored as it is dynamic but calculated and displayed everytime page load and I don't know how to do that.

CREATE PROCEDURE [dbo].[NivelGlobalRisc] 

AS 

DECLARE @result decimal(6,2),@ActivitateNGR decimal(6,2),@ZonaLucruNGR decimal(6,2),@CaracterSpecialNGR decimal(6,2)

SET	@ActivitateNGR = (SELECT CONVERT(decimal(4,2), Sum(NivelRisc))/CONVERT(decimal(4,2), Count(NivelRisc)) FROM tblActivitate)
SET	@ZonaLucruNGR = (SELECT CONVERT(decimal(4,2), Sum(NivelRisc))/CONVERT(decimal(4,2), Count(NivelRisc)) FROM tblZonaLucru)
SET	@CaracterSpecialNGR = (SELECT CONVERT(decimal(4,2), Sum(NivelRisc))/CONVERT(decimal(4,2), Count(NivelRisc)) FROM tblCaracterSpecial)


SET	@result = (@ActivitateNGR + @ZonaLucruNGR + @CaracterSpecialNGR)/3;

protected void Page_Load(object sender, EventArgs e)
{
    if(!IsPostBack)
    {
    }
    GetNGR();
}
protected void GetNGR()
{
    using (SqlConnection conn = new SqlConnection(connString))
    {
        using (SqlCommand cmd = new SqlCommand("NivelGlobalRisc", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();
            SqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                txtNGR.Text = rdr["@result"].ToString();
            }
            conn.Close();
        }
    }
}

How it should be done, please?

Amit Mohanty · Answer

To use output parameter modify your procesude as below CREATE PROCEDURE [dbo].[NivelGlobalRisc] @result DECIMAL(6, 2) OUTPUT AS BEGIN DECLARE @ActivitateNGR DECIMAL(6, 2), @ZonaLucruNGR DECIMAL(6, 2), @CaracterSpecialNGR DECIMAL(6, 2) SELECT @ActivitateNGR = CONVERT(DECIMAL(4, 2), SUM(NivelRisc)) / CONVERT(DECIMAL(4, 2), COUNT(NivelRisc)) FROM tblActivitate SELECT @ZonaLucruNGR = CONVERT(DECIMAL(4, 2), SUM(NivelRisc)) / CONVERT(DECIMAL(4, 2), COUNT(NivelRisc)) FROM tblZonaLucru SELECT @CaracterSpecialNGR = CONVERT(DECIMAL(4, 2), SUM(NivelRisc)) / CONVERT(DECIMAL(4, 2), COUNT(NivelRisc)) FROM tblCaracterSpecial SET @result = (@ActivitateNGR + @ZonaLucruNGR + @CaracterSpecialNGR) / 3; END Without optput parameter you can use like this CREATE PROCEDURE [dbo].[NivelGlobalRisc] AS BEGIN DECLARE @result decimal(6,2),@ActivitateNGR decimal(6,2),@ZonaLucruNGR decimal(6,2),@CaracterSpecialNGR decimal(6,2) SET @ActivitateNGR = (SELECT CONVERT(decimal(4,2), Sum(NivelRisc))/CONVERT(decimal(4,2), Count(NivelRisc)) FROM tblActivitate) SET @ZonaLucruNGR = (SELECT CONVERT(decimal(4,2), Sum(NivelRisc))/CONVERT(decimal(4,2), Count(NivelRisc)) FROM tblZonaLucru) SET @CaracterSpecialNGR = (SELECT CONVERT(decimal(4,2), Sum(NivelRisc))/CONVERT(decimal(4,2), Count(NivelRisc)) FROM tblCaracterSpecial) SET @result = (@ActivitateNGR + @ZonaLucruNGR + @CaracterSpecialNGR)/3; SELECT @result as Result; END protected void Page_Load(object sender, EventArgs e) { if(!IsPostBack) { } GetNGR(); } protected void GetNGR() { using (SqlConnection conn = new SqlConnection(connString)) { using (SqlCommand cmd = new SqlCommand("NivelGlobalRisc", conn)) { cmd.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { txtNGR.Text = rdr["Result"].ToString(); } conn.Close(); } } }

Jignesh Kumar · Answer

Oh I missed this, you did not select value after set in @result variable. You need to select @result at last o fyour

Marius Vasile · Answer

Ok, after studying more I found a possible solution using output parameters and change code behind with protected void GetNGR() { using (SqlConnection conn = new SqlConnection(connString)) { using (SqlCommand cmd = new SqlCommand("NivelGlobalRisc", conn)) { try { conn.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@result", SqlDbType.Decimal); cmd.Parameters["@result"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); txtNGR.Text = (string)cmd.Parameters["@result"].Value; } catch (Exception ex) { txtNGR.Text = "ErrorOccured: " + ex.Message.ToString(); } finally { conn.Close(); } } } } error message is "ErrorOccured: Procedure NivelGlobalRisc has no parameters and arguments were supplied."

Marius Vasile · Answer

Thank you Jignesh but I have no result displayed either

Jignesh Kumar · Answer

protected void Page_Load(object sender, EventArgs e) { if(!IsPostBack) { GetNGR(); } } protected void GetNGR() { using (SqlConnection conn = new SqlConnection(connString)) { using (SqlCommand cmd = new SqlCommand("NivelGlobalRisc", conn)) { cmd.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { txtNGR.Text = rdr["@result"].ToString(); } conn.Close(); } } }