Stored procedure with output parameters returns -1 in c#

Question

I have a simple c# application that uses the Entity Framework 5.0 and should call a stored procedure with output parameters. The problem is that it doesn't work, and instead, it returns -1.

The c# code that I have looks like that:

// Define the output paramaters
SqlParameter operatorID = new SqlParameter("@operatorID", SqlDbType.Int);
operatorID.Direction = ParameterDirection.Output;
SqlParameter operatorCode = new SqlParameter("@operatorCode", SqlDbType.Int);
operatorCode.Direction = ParameterDirection.Output;

var parameters = new List<object>();
parameters.Add(operatorID);
parameters.Add(operatorCode);

var noOutput = context.Database.ExecuteSqlCommand("EXEC my_sp_name @operatorID, @operatorCode", parameters.ToArray());

The stored procedure looks like that:

CREATE PROCEDURE [dbo].[my_sp_name] 
( 
     @operatorID        int             OUTPUT 
    ,@operatorCode      int             OUTPUT
 )
AS
    SET @operatorID = 123

I know it doesn't make sense, but it's just for a test.

The problem after executing the c# code is that the noOutput value is -1 and the operatorID value is empty, where I expect it to be 123.

Does anybody have an idea what I do wrong?

Suren Srapyan · Accepted Answer · 2015-10-28 07:38:44Z

6

Your parameters in query must be with OUTPUT keyword

var noOutput = context.Database.ExecuteSqlCommand("EXEC my_sp_name @operatorID OUTPUT, @operatorCode OUTPUT", parameters.ToArray());

answered Oct 28, 2015 at 7:38

Suren Srapyan

68.9k14 gold badges128 silver badges119 bronze badges

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

3 Comments

Apostrofix Over a year ago

Oh my GOD, are you kidding me? Thanks a lot! Adding the OUTPUT works fine, but I thought that it's not necessary since I have already defined them as output parameters.

Suren Srapyan Over a year ago

You must write OUTPUT.I have the same issue in MSSQL in query with OUTPUT Keyword

Apostrofix Over a year ago

I understand now. I'll accept the answer in 4 minutes.

Vijay Kumar · Accepted Answer · 2015-10-28 07:48:59Z

0

Check the "operatorCode.Value" property. the result will be there after execution.

If not try to use the CommandType.StoredProcedure and then execute the command with the store procedure name along with the parameters. you will get the output

answered Oct 28, 2015 at 7:48

Vijay Kumar

358 bronze badges

Collectives™ on Stack Overflow

Stored procedure with output parameters returns -1 in c#

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related