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

possible duplicate of Getting return value from stored procedure in C# — Renatas M.
– Renatas M., Commented Sep 12, 2012 at 10:33
why don't you just return the status and then you can easly read the returned value? — Alaa Jabre
– Alaa Jabre, Commented Sep 12, 2012 at 10:34
Try setting the @Status param's Direction to InputOutput and then read its value before closing your connection — Netricity
– Netricity, Commented Sep 12, 2012 at 10:59

RL89 · Accepted Answer · 2012-09-12 10:39:08Z

5

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.

answered Sep 12, 2012 at 10:39

RL89

1,9165 gold badges23 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sushant Over a year ago

above changes gives me this error: Procedure or function 'SP_Designation_Add' expects parameter '@Status', which was not supplied. >>How to solve this? I tried this solution already

RL89 Over a year ago

Can you please update your above code with your Current code.

Sushant Over a year ago

code is updated in my current scenario, when i add this to dbml it show i am declaring many anonymous colums. how to deal with them?

RL89 Over a year ago

In your Procedure you have declared @Status_Id int two times.Please Correct it once and let us know if its worked or not.

Sushant Over a year ago

-- Declare @Status_Id int -- Set @Status_Id= 0 is commented part in declare area!

|

Rab Khan · Accepted Answer · 2012-09-12 10:40:05Z

2

In Stored Procedure

RETURN @status

in C#

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

answered Sep 12, 2012 at 10:40

Rab Khan

35.7k4 gold badges52 silver badges66 bronze badges

2 Comments

Sushant Over a year ago

is it ok to get return in string? my return values are 1 or 2 or 3 only. initial value set to 0 for No Database Connection

Sushant Over a year ago

and what about public int? AddNewDesigDataSql(string desig_name, string Details, int AddedBy) ?? here int? replaced by string is ok?

AnandPhadke · Accepted Answer · 2012-09-13 06:56:01Z

0

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

edited Sep 13, 2012 at 6:56

answered Sep 12, 2012 at 10:40

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

4 Comments

Sushant Over a year ago

this is giving error like:

Error	53	Cannot implicitly convert type 'object' to 'System.Data.SqlClient.SqlParameter'. An explicit conversion exists (are you missing a cast?)	D:\<address>DesignationDAL.cs	35	47	LTS_DAL

Sushant Over a year ago

code is updated in my current scenario, when i add this to dbml it show i am declaring many anonymous colums. how to deal with them?

Sushant Over a year ago

no. its not returning status. Its giving my initial value 0 only. How to get the return value? In my project all operations i have done by linqtosql but i cant add this SP to .dbml as it containing multiple anonymous columns. What to do reconfigure the query or any solution available? Query is working fine in back end. i cant configure it to the front end!

Sushant Over a year ago

Unable to extract stored procedure 'dbo.Check_LeaveDays' because its result set contains multiple anonymous columns. This is the error. I got this error first time, dun know what to do further!

Collectives™ on Stack Overflow

Return value from stored procedure to c#

3 Answers 3

7 Comments

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related