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.
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.
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.
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.
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.
gilberk
0 Points
1 Post
Interesting problem with getting OUTPUT parameters from SQL Server using C#
Jan 21, 2008 10:46 AM|LINK
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.
Application Programming Issue asp.net sqltranaction .NET asp.net2.0 SqlNetFramework asp.net 2.0
szyder
Member
155 Points
39 Posts
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
Jan 21, 2008 01:32 PM|LINK
try to use return @test
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.
zirmandli
Member
750 Points
110 Posts
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
May 16, 2008 11:59 AM|LINK
Hi
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
-- Benazir Mandli
RealityO
Member
4 Points
17 Posts
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
Jun 09, 2009 06:43 PM|LINK
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.
bcline
Member
2 Points
1 Post
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
Jul 24, 2009 06:05 PM|LINK
I too am having the same problem. Has anyone found a solution?
ToddMoon
Member
10 Points
15 Posts
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
Jul 24, 2009 06:18 PM|LINK
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.
coderdave
Member
2 Points
1 Post
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
Aug 12, 2009 01:57 AM|LINK
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
junfengd
Member
2 Points
1 Post
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
Oct 27, 2009 11:23 PM|LINK
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.
believer7
Member
8 Points
4 Posts
Re: Interesting problem with getting OUTPUT parameters from SQL Server using C#
Nov 01, 2009 09:48 AM|LINK
Replace this (SET @test = 5) with select @test=5