February 11, 2008 at 5:49 am
Hi there
I am trying to achieve a very simple task.
I am trying to pass the return value of a stored procedure back to a .net application
My code is as follows :
Dim myConnection As New SqlConnection(strConn)
myConnection.Open()
Dim myCommand As New SqlCommand(strSQL, myConnection)
myCommand.CommandType = CommandType.Text
'Create a SqlParameter object to hold the output parameter value
Dim retValParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
'IMPORTANT - must set Direction as ReturnValue
retValParam.Direction = ParameterDirection.ReturnValue
'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(retValParam)
'Call the sproc...
myCommand.ExecuteNonQuery()
I run
declare @return_value INTEGER
exec @return_value = bft_4pm_snap 'CHECK','20080206'
Select @return_value
from query analyzer and I get 1 as expected
When I run this from .net I get 0.
I have added an output paramter in the stored procedure and return that, but it is still returning zero.
Can anybody help
February 11, 2008 at 8:07 pm
Can you post your stored procedure? Or at least the part where you are declaring your output variable and your return statement
February 12, 2008 at 2:22 am
Here are relevant bits , thanks
--bft_4pm_snap 'CHECK','20080205'
CREATE procedure bft_4pm_snap @operation VARCHAR(10),@as_of_dt DATETIME, @return_value int = null output
as
--Declare @operation VARCHAR(10)
--declare @as_of_dt DATETIME
--set @as_of_dt ='20080204'
.
.
.
.
.
.
if @operation ='CHECK'
BEGIN
select @rowcount = COUNT(*) from #fwd_rates
if @rowcount >0
begin
set @return_value =1
Return @return_value
end
else
begin
set @return_value =0
Return @return_value
end
END
else
select * from #fwd_rates
GO
February 21, 2008 at 4:46 am
For an output parameter like that, I've been using ParameterDirection.Output - Which works well for me, after some issues with ensuing the names were all correct.
As an example:
SqlParameter[] sqlParamsMain = new SqlParameter[1];
sqlParamsMain[0] = new SqlParameter();
sqlParamsMain[0].SqlDbType = System.Data.SqlDbType.UniqueIdentifier;
sqlParamsMain[0].ParameterName = "@GUID";
sqlParamsMain[0].Direction = System.Data.ParameterDirection.Output;
I know it's a longwinded way of declaring them, but it works Ok for me 🙂
As you are doing ExecuteNonQuery, I think the output parameter is the way. If you were doing ExecuteScalar your return value might work, but I haven't tested that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply