Click here to Skip to main content
15,891,253 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hello,
I want to get return value from stored procedure,
I am unable to get it,

Below is my Sql and C# code.

Ms Sql Stored Procedure

SQL
ALTER PROCEDURE mat.CheckRequestStatus

	@RequestFrom VARCHAR(64),
	@RequestTo VARCHAR(64),
	@RequestType INTEGER,
	
AS
BEGIN
		DECLARE @RequestStatus INTEGER				
		SET  @RequestStatus=(SELECT ISNULL((SELECT DISTINCT RequestStatus FROM tblRequest WHERE RequestFrom=@RequestFrom AND @RequestTo=@RequestTo), 0))
		
		IF( @RequestStatus=0)
		BEGIN
		SET  @RequestStatus=0
		RETURN @RequestStatus
		END
		
		RETURN @RequestStatus					
END



C# Code

C#
public int CheckRequestStatus(string RequestFrom, string RequestTo,int RequestType)
        {
            /* function CheckInterestReuest Returns a value
             * '0' for Request Not Exist
             * '1' for Pending Request
             * '2' for Accepet
             * '3' for Reject
             */

            int RequestStatus = 0;

            try
            {
                if (sqlcon.State == ConnectionState.Closed)
                {
                    sqlcon.Open();
                }

                SqlCommand cmd = new SqlCommand("mat.CheckRequestStatus", sqlcon);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@RequestFrom", RequestFrom);
                cmd.Parameters.AddWithValue("@RequestTo", RequestTo);
                cmd.Parameters.AddWithValue("@RequestType", RequestType);

                SqlParameter RuturnValue = new SqlParameter("@RequestStatus", SqlDbType.Int);
                RuturnValue.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(RuturnValue);
                cmd.ExecuteNonQuery();
                RequestStatus = (int)cmd.Parameters["@RequestStatus"].Value;
            }
            catch
            {

            }
            finally
            {
                sqlcon.Close();
            }

            return RequestStatus;
        }


What I have tried:

I am trying to get return value from stored procedure
Posted
Updated 8-Dec-16 7:34am

Solution 1 shows you how to get an output parameter from a Stored Procedure. But you already know how to do this as you asked a very similar question earlier that dealt with output parameters.

To get the return value you need still to add an appropriate parameter to the collection for cmd but this time it is a Return Value, i.e.
C#
cmd.Parameters.Add("@retValue", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;
Then after you have executed the stored procedure you get the return value as you would any other parameter (which you already know how to do)
C#
int retval = (int)cmd.Parameters["@retValue"].Value;

In cases like this the official documentation is a wonderful resource - I suggest you try it : System.Data.SqlClient Namespace[^]
 
Share this answer
 
Comments
Vikas Hire 30-Dec-16 5:32am    
I mean where I should declare @retVal ..?
Vikas Hire 30-Dec-16 5:38am    
I update my Code as you suggest but I got this error..

Procedure or function 'userRegistration' expects parameter '@retValue', which was not supplied.
CHill60 5-Jan-17 7:04am    
Are you sure you added that parameter as the correct type?
You've marked the answer as complete so I guess you got it to work
Vikas Hire 5-Jan-17 7:49am    
work out.. :)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900