Need a return value from a stored procedure in C#
.net, c#, sql, stored-procedures
Solution
In this case, you can use the ExecuteScalar() method of Command to return the value. ExecuteScalar will look at the first column of the first record returned from the database.
Problem
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#?