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#](http://stackoverflow.com/questions/706361/getting-return-value-from-stored-procedure-in-c-sharp) — Renatas M., Sep 12 '12 at 10:33
why don't you just return the status and then you can easly read the returned value? — Alaa Jabre, Sep 12 '12 at 10:34
http://stackoverflow.com/questions/706361/getting-return-value-from-stored-procedure-in-c-sharp something duplicate — Gyan Chandra Srivastava, Sep 12 '12 at 10:37
Try setting the @Status param's Direction to InputOutput and then read its value before closing your connection — Netricity, Sep 12 '12 at 10:59

score 5 · Accepted Answer · answered Sep 12 '12 at 10:39

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 '12 at 10:39

RL89

1,866
5
22
39

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 – Sushant Sep 12 '12 at 11:23
Can you please update your above code with your Current code. – RL89 Sep 12 '12 at 11:33
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 Sep 13 '12 at 04:41
In your Procedure you have declared **@Status_Id int** two times.Please Correct it once and let us know if its worked or not. – RL89 Sep 13 '12 at 07:33
`-- Declare @Status_Id int -- Set @Status_Id= 0 ` is commented part in declare area! – Sushant Sep 13 '12 at 08:25
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/16628/discussion-between-rahul-and-sushant) – RL89 Sep 13 '12 at 08:27
chattin session was really helpful... thaank you budddy!! – Sushant Sep 13 '12 at 10:52

score 2 · Answer 2 · answered Sep 12 '12 at 10:40

2

In Stored Procedure

RETURN @status

in C#

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

answered Sep 12 '12 at 10:40

Raab

34,778
4
50
65

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 Sep 12 '12 at 11:05
and what about `public int? AddNewDesigDataSql(string desig_name, string Details, int AddedBy)` ?? here `int?` replaced by `string` is ok? – Sushant Sep 12 '12 at 11:26

AnandPhadke · Answer 3 · 2012-09-13T06:56:01.977

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

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:\
DesignationDAL.cs 35 47 LTS_DAL ` — Sushant, Sep 12 '12 at 11:14
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, Sep 13 '12 at 04:41
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, Sep 13 '12 at 06:49
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! — Sushant, Sep 13 '12 at 07:14

Return value from stored procedure to c#

3 Answers3

Linked