SQL Server Home
SQL Server 2008, Stored Procedure "out" parameter of type "int" returns null value.
as Not Reproducible
2/3/2011 8:13:20 AM
User(s) can reproduce this bug
I am executing a stored procedure from my C# .NET application. I am expecting an int value in a "out" parameter of that procedure.
However, sometimes I receive "null" value in that Stored Procedure.
I did confirm using SQL Profiler that the parameter values are passed correctly to the procedure and further the procedure is also getting executed successfully. The "out" variable is properly initialized and we are setting the record count value in that "out" parameter hence it can not be null.
But still on accessing the value of "out" parameter from the .NET app after the execution of the query, I receive null value.
FYI I am using ExecuteNonQuery Statement for executing the above stored procedure.
I am getting this issue once in every five-ten times call to that stored procedure.
This is evolving as a major issue with my application resulting into a blocker.
The issue is very urgent.
Any help would be appreciated.
SQL Server 2008 - Express Edition
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
Please find a sample stored procedure that I am executing for your reference.
CREATE PROCEDURE [dbo].[DemoProc]
@iretValue int out
SET NOCOUNT ON
DECLARE @recordCount int
SELECT @recordCount=COUNT(*) FROM [DemoTable1]
INNER JOIN DemoTable2 on DemoTable2.Id=[DemoTable1].Id
WHERE upper([DemoTable1].User) = upper(@user) AND [DemoTable1].Pwd=@pwd
SET @iretValue= @recordCount
SET NOCOUNT OFF
I have written PsuedoNames in the above code due to customer's IPR.
This is very critical issue.
Upon executing this procedure for around 5-10 times, sometimes on accessing the value of parameter "@iretValue" from my .NET app, I get null value.
@iretValue should return either 0 or 1 in my case.
to post a comment.
Please enter a comment.
on 5/30/2012 at 12:32 PM
Closing the bug as we have not heard back from you.
on 6/20/2011 at 3:22 PM
We've going ahead with closing this Connect bug, since we are not able to reproduce this issue. If you are still seeing this issue, please feel free to re-activate with your repro and we will investigate further.
ADO.NET Test Lead
on 5/9/2011 at 10:32 AM
I have tried to reproduce this issue, here is the sample code I am using:
using (SqlCommand command = new SqlCommand("[DemoProc]", connection))
command.CommandType = CommandType.StoredProcedure;
var iretValuePar = command.Parameters.Add("@iretValue", SqlDbType.Int);
iretValuePar.Direction = ParameterDirection.Output;
int iretValue = (int)iretValuePar.Value;
The return value of this sample code using your stored procedure is not NULL (Console.WriteLine prints the number returned back). In order to receive OUT parameter's value back, the parameter should be defined with exactly the same type and with direction set to Output (see the code).
In order to help you the best, please post your .Net code, we need to know how you declare and use the command and its parameters.
ADO.NET Development team
on 5/3/2011 at 2:22 PM
Thanks for reporting this issue. We will investigate it and report it back to you.
to post a workaround.
Please enter a workaround.
© 2014 Microsoft