Need a return value from a stored procedure in C#

Question

With the following Stored Procedure in SQL Server 2005

IF OBJECT_ID('[dbo].[UserProfile]') IS NOT NULL

DROP PROCEDURE [dbo].[UserProfile]  

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[UserProfile] 
(      
      @UserId VARCHAR(20)
)

AS

IF @UserId = 'userId'
BEGIN
SELECT @UserId = 'Yes'
END

ELSE
SELECT @UserId = 'No'

  SELECT @UserId AS RESULT 

  RETURN RESULT

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

C# code, needs to obtain the result of the stored procedure

public String UserProfile(string userId)
{
   String result;
   System.Data.Common.DbCommand cmd = this.mConn.CreateCommand();

   try
   {

       cmd.CommandTimeout = this.mCmdTimeout;
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.CommandText = "UserProfile";

       DbParameter p = cmd.CreateParameter();

       p.ParameterName = "@UserId";
       p.DbType = DbType.String;
       p.Value = userId;

       cmd.Parameters.Add(p);

       cmd.Connection.Open();
       cmd.ExecuteNonQuery();
       cmd.Connection.Close();

       //Need to assign the value of the stored procedure's return to result
       result = Convert.ToString(cmd.Parameters["@UserId"].Value);
   }
   catch (Exception ex)
   {
       throw;
   }
   finally
   {
       cmd.Connection.Close();
   }

   return result;
}

How do I return the result of my stored procedure in C#?

Answer 1

I was also looking for returning value from Stored procedure and capture it in C#.

I was returning various return codes based on the logic in my code, and executescalar will not help as I wanted to return out of stored proc in between wherever required with the appropriate code and not use select. So by adding an extra parameter with Direction as ReturnValue, you can capture the return value. No need of OUT parameters to capture return value from stored proc.

       preturn.ParameterName = "@Return_Value";  
       preturn.DbType = DbType.Int;        
       preturn.Direction = ParameterDirection.ReturnValue;

So here is what I came up with. Editing the original code in this post to keep it simple.

public String UserProfile(string userId)  
{  
   String result;  
   System.Data.Common.DbCommand cmd = this.mConn.CreateCommand();  

   try  
   {  

       cmd.CommandTimeout = this.mCmdTimeout;  
       cmd.CommandType = CommandType.StoredProcedure;  
       cmd.CommandText = "UserProfile";  

       DbParameter p = cmd.CreateParameter();  

       p.ParameterName = "@UserId";  
       p.DbType = DbType.String;  
       p.Value = userId;  

       cmd.Parameters.Add(p);  

       DbParameter preturn = cmd.CreateParameter();        
       preturn.ParameterName = "@Return_Value";  
       preturn.DbType = DbType.Int;        
       preturn.Direction = ParameterDirection.ReturnValue;

       cmd.Parameters.Add(preturn);  

       cmd.Connection.Open();  
       cmd.ExecuteNonQuery();  
       cmd.Connection.Close();  

       //Need to assign the value of the stored procedure's return to result  
       result = Convert.ToString(cmd.Parameters["@Return_Value"].Value);  
   }  
   catch (Exception ex)  
   {  
       throw;  
   }  
   finally  
   {  
       cmd.Connection.Close();  
   }  

   return result;  
}