Calling stored procedure with return value

Question

I am trying to call a stored procedure from my C  windows application  The stored procedure is running on a local instance of SQL Server 2008  I am able to call the stored procedure but I am not able to retrieve the value back from the stored procedure  This stored procedure is supposed to return the next number in the sequence  I have done research online and all the sites I ve seen have pointed to this solution working   Stored procedure code   ALTER procedure  dbo   usp GetNewSeqVal         SeqName nvarchar 255  as begin       declare  NewSeqVal int       set NOCOUNT ON       update AllSequences       set  NewSeqVal   CurrVal   CurrVal Incr       where SeqName    SeqName        if   rowcount   0 begin print  Sequence does not exist              return       end        return  NewSeqVal end   Code calling the stored procedure   SqlConnection conn   new SqlConnection getConnectionString     conn Open     SqlCommand cmd   new SqlCommand parameterStatement getQuery    conn   cmd CommandType   CommandType StoredProcedure   SqlParameter param   new SqlParameter     param   cmd Parameters Add   SeqName   SqlDbType NVarChar   param Direction   ParameterDirection Input  param Value    SeqName    SqlDataReader reader   cmd ExecuteReader      I have also tried using a DataSet to retrieve the return value with the same result  What am I missing to get  the return value from my stored procedure  If more information is needed  please let me know

User · Answer

This is a very short sample of returning a single value from a procedure:

SQL:

CREATE PROCEDURE [dbo].[MakeDouble] @InpVal int AS BEGIN 
SELECT @InpVal * 2; RETURN 0; 
END

C#-code:

int inpVal = 11;
string retVal = "?";
using (var sqlCon = new SqlConnection(
    "Data Source = . ; Initial Catalog = SampleDb; Integrated Security = True;"))
{
    sqlCon.Open();
    retVal = new SqlCommand("Exec dbo.MakeDouble " + inpVal + ";", 
        sqlCon).ExecuteScalar().ToString();
    sqlCon.Close();
}
Debug.Print(inpVal + " * 2 = " + retVal); 
//> 11 * 2 = 22

User · Answer

ExecuteScalar    will work  but an output parameter would be a superior solution

User · Answer

You can try using an output parameter  http   msdn microsoft com en-us library ms378108 aspx

User · Answer

I had a similar problem with the SP call returning an error that an expected parameter was not included   My code was as follows  Stored Procedure         Result int OUTPUT   And C                 SqlParameter result   cmd Parameters Add new SqlParameter   Result   DbType Int32                result Direction   ParameterDirection ReturnValue     In troubleshooting  I realized that the stored procedure was ACTUALLY looking for a direction of  InputOutput  so the following change fixed the problem               r      Result Direction   ParameterDirection InputOutput

User · Answer

I see the other one is closed  So basically here s the rough of my code  I think you are missing the string cmd comment  For example if my store procedure is call DBO Test  I would need to write cmd  DBO test   Then do command type equal to store procedure  and blah blah blah  Connection open    String cmd  DBO test     the command Sqlcommand mycommand

User · Answer

Or if you re using EnterpriseLibrary rather than standard ADO NET     Database db   DatabaseFactory CreateDatabase    using  DbCommand cmd   db GetStoredProcCommand  usp GetNewSeqVal          db AddInParameter cmd   SeqName   DbType String   SeqNameValue        db AddParameter cmd   RetVal   DbType Int32  ParameterDirection ReturnValue  null  DataRowVersion Default  null        db ExecuteNonQuery cmd        var result    int cmd Parameters  RetVal   Value

User · Answer

I know this is old  but i stumbled on it with Google   If you have a return value in your stored procedure say  Return 1   - not using output parameters   You can do the following -   RETURN VALUE  is silently added to every command object  NO NEED TO EXPLICITLY ADD      cmd ExecuteNonQuery        rtn    int cmd Parameters   RETURN VALUE   Value

User · Answer

You need to add return parameter to the command   using  SqlConnection conn   new SqlConnection getConnectionString     using  SqlCommand cmd   conn CreateCommand          cmd CommandText   parameterStatement getQuery        cmd CommandType   CommandType StoredProcedure      cmd Parameters AddWithValue  SeqName    SeqNameValue         var returnParameter   cmd Parameters Add   ReturnVal   SqlDbType Int       returnParameter Direction   ParameterDirection ReturnValue       conn Open        cmd ExecuteNonQuery        var result   returnParameter Value

User · Answer

The version of EnterpriseLibrary on my machine had other parameters  This was working           SqlParameter retval   new SqlParameter   ReturnValue   System Data SqlDbType Int           retval Direction   System Data ParameterDirection ReturnValue          cmd Parameters Add retval           db ExecuteNonQuery cmd           object o   cmd Parameters   ReturnValue   Value

[c#] Calling stored procedure with return value

Examples related to c#

Examples related to sql-server

Examples related to stored-procedures

Examples related to ado.net

Examples related to return-value