I have the following SP:

Create procedure dbo.example

(

@test INT OUTPUT

)

AS

BEGIN

SET @test = 5

SELECT * from Users

END

GO

and when I try calling it from C# using an SqlCommand with adding an output parameter and getting it's value, I get that the value is NULL. (I should mention that I use ExecuteReader)

However,  if I alter the SP and remove the select line, the c# code works and returns the value 5.

try to use return @test

szyder:

try to use return @test

 
I am having the exact same problem. This reply is not helpful because the stored procedure may have multiple output parameters. When using return, you will only be able to return one value.

EDIT: To clarify my problem, and to contradict the original post, the value of that output parameter is not NULL (DbNull.Value), it's null (good old-fashioned C# null). Which implies that the framework is not assigning any value at all to the output parameter after execution of the command. Actually, when I set a value to the parameter before executing the command, it gets replaced with null after execution. That's even more strange.

More Info:

If I execute the stored proc in Visual Studio and examine the results in the output window, it behaves exactly as expected. I see my query results, the value of my output parameter, and a @RETURN_VALUE of zero (the default if you don't use a RETURN statement)

If I don't assign to the output parameter (which I named @ReturnCode) in the proc then I get <NULL> in the window. So, again, it's working exactly as expected when executing in VS. It seems that the SqlClient library is to blame.

With SELECT and setting the output param:

MacAddress  
------------
0002680117E7
No rows affected.
(1 row(s) returned)
@ReturnCode = 0
@RETURN_VALUE = 0

 
With SELECT and not setting output param:

MacAddress  
------------
0002680117E7
No rows affected.
(1 row(s) returned)
@ReturnCode = <NULL>
@RETURN_VALUE = 0
 

From these results it's clear that it is being set when I set it to zero in the proc (Or returned as NULL if I don't set it), but it's always null when the SqlClient framework finishes executing it.

object objRetTest;

 I'm bumping a one year old thread:

 As anyone ever fixed this issue? I'm currently having the same exact problem and there's no solution in the thread.

 I too am having the same problem.  Has anyone found a solution?

There's a simple but ugly workaround. Rather than use output parameters, select a second result set that contains one row with one column for each output variable you need. You will then have two result sets.

SELECT * FROM SomeTable --This is your normal select result

SELECT @Foo AS Foo, @Bar AS Bar, @Zap AS Zap --These are your "output parameters"

Of course, you would remove the actual output parameters from the stored procedure if doing this, and just use local variables.

If using a SqlDataReader you can use the NextResult() method to move to the next result. If using a SqlDataAdapter with a DataSet, you should get a second DataTable.

RealityO:

As anyone ever fixed this issue? I'm currently having the same exact problem and there's no solution in the thread.

Try the ZipZango Database Library. ( www.zipzango.com ) I wrote it after continually running into the same type of issue. Download the demo. If you think it will work for you, drop me an email and I'll arrange to get you a full copy for free.

Regards,

-DG

Since row sets will be returned before all output parameters for a stored procedure. I guess that you will need to either retrieve or skip all row sets and then getting the output parameters. 

Replace this (SET @test = 5) with select @test=5