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

Marius Vasile

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?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty2y ago

Accepted 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 Kumar2y ago
Oh I missed this, you did not select value after set in @result variable. You need to select @result at last o fyour

Marius Vasile2y ago

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 Vasile2y ago
Thank you Jignesh but I have no result displayed either

Jignesh Kumar2y ago

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();
        }
    }
}