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.
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.
ToddMoon
Member
10 Points
15 Posts
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
Apr 30, 2008 08:46 PM|LINK
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.