Microsoft Application Blocks

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

believer7 — Sun, 01 Nov 2009 10:48:12 GMT

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

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

junfengd — Tue, 27 Oct 2009 23:23:26 GMT

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.

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

coderdave — Wed, 12 Aug 2009 01:57:23 GMT

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

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

ToddMoon — Fri, 24 Jul 2009 18:18:02 GMT

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.

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

bcline — Fri, 24 Jul 2009 18:05:56 GMT

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

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

RealityO — Tue, 09 Jun 2009 18:43:26 GMT

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.

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

zirmandli — Fri, 16 May 2008 11:59:05 GMT

After declaring variables Execute your command

db.ExecuteNonQuery(dbCommandWrapper);

object objRetTest;

objRetTest= db.GetParameterValue(dbCommandWrapper, "Your variable name that u passes ");

if (objRetTest== DBNull.Value) return -1;

else

// return output parameter values

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

ToddMoon — Wed, 30 Apr 2008 20:46:41 GMT

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.

Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#

szyder — Mon, 21 Jan 2008 14:32:44 GMT

try to use return @test

Interesting problem with getting OUTPUT parameters from SQL Server using C#

gilberk — Mon, 21 Jan 2008 11:46:03 GMT

I have the following SP:

Create procedure dbo.example

(

@test INT OUTPUT

)

BEGIN

SET @test = 5

SELECT * from Users

END

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.