how to get return value from procedure sql server 2005 to c#

Question

CREATE PROCEDURE [dbo].[Code]           
@intEpmName  NUMERIC,            
@strFailedEMPID VARCHAR(1000) output       
AS  

DECLARE    
@FailedCodes VARCHAR(1000)
BEGIN 
----
my  logic where  i need  return the value
SET @strFailedEMPID = @FailedCodes  
----- 
END

In the stored procedure above, I can send the value as "0" to @strFailedEMPID then to my procedure. However, when I return the value from my procedure, then to the same variable @strFailedEMPID I am sending the value as such:

lsqlParam = new SqlParameter("@strFailedEMPID ", SqlDbType.VarChar);
lsqlParam.Value = "0";
lsqlParam.Direction = ParameterDirection.ReturnValue;
lsqlCmd.Parameters.Add(lsqlParam);

Can anyone help with the correct syntax to get the return value from the procedure?

You need to declare the ParameterDirection as Output not ReturnValue in your C# code — codingbadger
– codingbadger, Commented Mar 14, 2011 at 12:55

AdaTheDev · Accepted Answer · 2011-03-14 12:59:15Z

1

It's because you are defining the parameter in .NET as a ReturnValue which would actually equate to the scenario where you use RETURN within the stored procedure to return an integer (which you're not doing).

Instead, you need to define the @strFailedEMPID parameter as ParameterDirection.Output within your .NET code. If you want to pass a value in AND receive one out through the parameter, use ParameterDirection.InputOutput.

After executing the sproc, you then just:

string value = lsqlCmd.Parameters["@strFailedEMPID"].value;

So....

lsqlParam = new SqlParameter("@strFailedEMPID ", SqlDbType.VarChar);
lsqlParam.Value = "0";
lsqlParam.Direction = ParameterDirection.InputOutput;
lsqlCmd.Parameters.Add(lsqlParam);

lsqlCmd.ExecuteNonQuery();
string value = lsqlCmd.Parameters["@strFailedEMPID"].value;

answered Mar 14, 2011 at 12:59

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to get return value from procedure sql server 2005 to c#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related