Executescalar receives wrong value when stored procedure returns value

Question

I am trying to return a single value to c# using the executescalar method.

When I execute the below stored procedure in SQL Server, the if..blocks are working fine but executescalar in c# always returns 0.

Please refer to the below code:

USE [xx]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[prcAddress]
    @ID int, 
    @Name varchar(50),
    @Designation varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @count as Integer -- To count records
    Declare @Result int -- To return result

    SELECT @Result=0
    SELECT @count = (SELECT count(*) FROM dbo.Address)

    IF @ID >0
    BEGIN
    --Update the current entry
    SELECT @Result=1
    END
    ELSE IF @ID =0 AND @count=0
    BEGIN
        -----do something
    SELECT @Result=2
    END
    ELSE IF @ID=0 AND @count>0
    BEGIN
    ----do something
    SELECT @Result=3
    END
    ELSE
    BEGIN
    SELECT @Result=4
    END

    SELECT @Result As Result
END
GO

           SqlConnection sqlCon = new SqlConnection(ConnectionString);
            SqlCommand sqlCom = new SqlCommand();

            try
            {
                sqlCom = new SqlCommand("prcAddress", sqlCon);
                sqlCom.CommandType = CommandType.StoredProcedure;
                sqlCom.CommandTimeout = 15;
                sqlCom.Connection = sqlCon;
                foreach (KeyValuePair<Object, Object> parmater in parameters)
                {
                    if (parmater.GetType() == typeof(DateTime))
                    {
                        sqlCom.Parameters.Add("@" + parmater.Key, SqlDbType.DateTime).Value = parmater.Value;
                    }
                    else
                    {
                        sqlCom.Parameters.AddWithValue("@" + parmater.Key, parmater.Value);
                    }
                }

                if (sqlCon.State != ConnectionState.Closed)
                {
                    sqlCon.Close();
                }
                sqlCon.Open();
                if (sqlCom.ExecuteScalar() != null)
                {
                    result = sqlCom.ExecuteScalar().ToString();
                }
                else
                {
                    result = "";
                }
            }
            catch (SqlException sqlEx)
            {
                System.Web.HttpContext.Current.Response.Redirect("~/Error.aspx", false);
            }
            finally
            {
                sqlCon.Close();
                sqlCom = null;
            }

Answer 1

You're probably seeing the result of the first select, 'SELECT @Result=0'. Either comment out all the selects prior to the last select in your stored procedure or change it to a scalar function that returns the result.