Return value from stored procedure to c#

Question

I got same problem in one more stored procedure. I want to add leave request to a SQL Server 2008 database. I can run a stored procedure successfully but from page I can't return status whether operation is complete or incomplete.

I am sending data through SP but not able to get status through program.

My stored procedure is:

ALTER Procedure [dbo].[Check_LeaveDays](
@EmpCode int,
@LV_Type int,
@Leave_AppDt DateTime,
@Leave_ToDate Datetime ,
@LV_Days int
,@Status_Id int Output
)

as
Begin 

    Declare @Dt_join datetime ,@LastDate Datetime
    Declare @Count int 
    Declare @Leave_Status_Id int 
    Declare @Leave_period int 
    Declare @Rule_id int 
    Declare @Leave_AllocatedDays int
    Declare @Leave_MaxDays int 
    Declare @Days_diff int
--  Declare @Status_Id int
--  Set @Status_Id= 0
        Select @Dt_Join =Emp_DOJ from LTS_Employee_Master where Emp_ID =4

        Select @LastDate= DATEADD(year, DATEDIFF(year, -1, getdate()), -1)
        Select @Days_diff=0

        If(YEAR(@Dt_Join) =  YEAR(GETDATE()))
        Begin 
            Select @Days_diff = DATEDIFF(D, @Dt_Join,@LastDate) 
        End 


    --Select @Leave_AppDt = dateadd(M, -2, getdate())


            Select @Rule_id = Case when @LV_Type =1 then ISNULL(Emp_Casual_rule,0)
            when @LV_Type =2 then ISNULL(Emp_Medical_rule,0)  
            when @LV_Type =3 then ISNULL(Emp_PL_rule,0)  
                    else 0 End 
                from LTS_Employee_Master where Emp_ID =@Empcode 

    If @LV_Type =1
    Begin 
                Select @Leave_AllocatedDays = LPM_Allocated_Days ,@Leave_MaxDays =LPM_Max_Days ,@Leave_period =LPM_Count 
                from LTS_Leave_Policy_Master where LPM_Id =@Rule_Id 
    If   @Days_diff <> 0
    Begin 
            Select @Leave_AllocatedDays = 365/@Leave_AllocatedDays
            Select @Leave_AllocatedDays = @Days_diff / @Leave_AllocatedDays
    End
                Select  @Count =Sum(Leave_Days)  
                from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=1 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date) 

                    Select  @Count = ISNULL(@Count,0) + ISNULL(Sum(Leave_Days),0)  
                    from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=3 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date) 
                    and Req_id not in(Select Req_id   from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=3 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date)) 

                Select  @Count = ISNULL(@Count,0) + ISNULL(Sum(Leave_Days),0)  
                from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=3 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date) 
                and Req_id not in(Select Req_id   from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID=3 and YEAR(@Leave_ToDate) =YEAR(leave_to_Date)) 

                Select @count,@Leave_MaxDays 

    if(@LV_Days > @Leave_MaxDays)
    Begin  

                Set @Status_Id=1 -- Status appliation leave days is more than allowance max days at a time 

    End  


    If(@Count  > @Leave_AllocatedDays)
    Begin 
                Select @Status_Id =2 --Status 2 applies for numbers of maximum  days applied is more than actual allocated maximum number of days 

    End 

                Select  @Count =Sum(Leave_Days)  
                from
                (Select  top 1  *  from  LTS_Emp_Leave_Requests  order by Leave_ID desc) Temp
                where Leave_Emp_ID =@empcode and Leave_type_Id=1 and Leave_Status_ID =1   group by Leave_Emp_Id

                Declare @tbl table(Leave_Id int , Leave_Status_Id int , Leave_To_date datetime,leave_days int,
                Leave_Emp_Id int,Leave_off_Id int,Req_Id int,leave_LPM_ID int, leave_type_Id int)
                Insert into @tbl 
                Select top 1
                Leave_Id  , Leave_Status_Id , Leave_To_date ,
                Leave_days ,Leave_Emp_Id ,Leave_off_Id ,Req_Id ,leave_LPM_ID , Leave_type_Id 
                from LTS_Emp_Leave_Requests where Leave_Emp_ID =@empcode and Leave_Status_ID  in(1,3,5) order by Leave_ID desc

    Select @Leave_ToDate =Leave_To_date from @tbl 
    If( DATEDIFF(D,@Leave_ToDate, DATEADD(D,-1, @Leave_AppDt)) > @Leave_AllocatedDays)
    Begin
            Select @Status_Id =3
    End  
    End 
    Return @Status_Id

End

My stored procedure calling functions:

public string SendRequestDataSql(int empid, int leavetype, DateTime fromdate, DateTime todate, int leavedays)
    {
        string retunvalue="0";
        DataTable dt = new DataTable();
        try
        {
            SqlConnection sqlConnection = new SqlConnection();
            string conString = Connection.GetConnection;

            using (SqlConnection con = new SqlConnection(conString))
            {
                //sqlConnection.Open();
                using (SqlCommand cmd = new SqlCommand("Check_LeaveDays", con))
                {      


                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@EmpCode", SqlDbType.VarChar, 10).Value = empid;
                    cmd.Parameters.Add("@LV_Type", SqlDbType.VarChar, 10).Value = leavetype;
                    cmd.Parameters.Add("@Leave_AppDt", SqlDbType.VarChar,15).Value = fromdate;
                    cmd.Parameters.Add("@Leave_ToDate", SqlDbType.VarChar,15).Value = todate;
                    cmd.Parameters.Add("@LV_Days", SqlDbType.VarChar,10).Value = leavedays;

                 SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int); 
                    returnParameter.Direction = ParameterDirection.ReturnValue; 

                    con.Open();
                    int itrrr = Convert.ToInt32( cmd.ExecuteNonQuery());

                    int returnValue = (int)returnParameter.Value; 
                    //message = Convert.ToInt32(objparm.Value);

                    con.Close();




                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        dt = ds.Tables[0];
                    }
                }
            }
        }
        catch (SqlException ex)
        {

        }
        return retunvalue;
    }

Answer 1

public int? AddNewDesigDataSql(string desig_name, string Details, int AddedBy)
    {
        int? status = 0;
        DataTable dt = new DataTable();
        try
        {
            SqlConnection sqlConnection = new SqlConnection();
            string conString = Connection.GetConnection;

            using (SqlConnection con = new SqlConnection(conString))
            {
                con.Open();

                using (SqlCommand cmd = new SqlCommand("SP_Dedignation_Add", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter param1=cmd.Parameters.Add("@Desig_Name", SqlDbType.varchar,500).Value = desig_name;
 param1.Direction = ParameterDirection.Input;

                    SqlParameter param2=cmd.Parameters.Add("@Desig_Desc", SqlDbType.varchar,500).Value = Details;
param2.Direction = ParameterDirection.Input;

                    SqlParameter param3=cmd.Parameters.Add("@Desig_AddedBy", SqlDbType.int,8).Value = AddedBy;
param3.Direction = ParameterDirection.Input;

                    SqlParameter param4=cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;

param4.Direction = ParameterDirection.Output;


                    cmd.ExecuteNonQuery();
                    con.Close();

                    status = (int)param4.Value;

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        dt = ds.Tables[0];
                    }
                }
            }
        }
        catch (SqlException ex)
        {

        }
        return status;
    }

Answer 2

In Stored Procedure

RETURN @status

in C#

string retunvalue = (string)sqlcomm.Parameters["@status"].Value;

Answer 3

Please Edit Your Code as:

Remove this line:

 cmd.Parameters.Add("@Status", SqlDbType.Int).Value = status;

Add this Code:

 SqlParameter abc = cmd.Parameters.Add("@Status", SqlDbType.Int);
 abc.Direction = ParameterDirection.Output;

Then you can get your Status result in abc.

Hope this will help you.