Getting Return Value From Stored Proccedure (Part 2)

  • Thread starter Thread starter Mick Walker
  • Start date Start date
M

Mick Walker

Public Sub CheckProduct(ByVal _ConnString As String, ByVal ProductList
As List(Of Import_ImportLines.Lines))
Dim ReturnValue As Integer = 0 ' Our Return Value
Dim conn As New SqlConnection
Dim cmd As New SqlCommand
conn.ConnectionString = _ConnString
cmd.Connection = conn
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "dbo.ImportLinesProductExists"

Dim Item As New Import_ImportLines.Lines
For Each Item In ProductList
Dim param As New SqlParameter

param = cmd.Parameters.Add("@Retval", SqlDbType.Int)
param.Direction = ParameterDirection.Output
param = cmd.Parameters.Add("@SupplierSKU",
SqlDbType.VarChar, 50)
param.Value = Item.supplierSKUCode
param.Direction = ParameterDirection.Input

Try
conn.Open()
cmd.ExecuteNonQuery()
ReturnValue =
Convert.ToInt32(cmd.Parameters("@RetVal").Value)
Catch ex As SqlException
Throw ex
Finally
conn.Close()
End Try

If ReturnValue = 0 Then
InsertTempProducts(_ConnString, Item)
End If
Next
conn.Close()
End Sub

Could anyone please tell me where I am going wrong with trying to get a
return value from a stored proccedure.

Regards
 
you are fetching the value of a parameter, not the return value of a
proc. for a proc parameter to return a value to the caller, the proc
must declare it as a output parameter as sql defaults to pass by value.

create procdure test1 @i1 int, i2 int
as
set @i1 = @i2;
return @i1;
create procdure test2 @i1 int out, i2 int
as
set @i1 = @i2;
return @i1;

.....

declare @r int, @i1 int;
exec @r = test1 @i,2; -- @r = 2 @i = null
exec @r = test2 @i,2; -- @r = 2 @i = null
exec @r = test2 @i out,2; -- @r = 2 @i = 2


-- bruce (sqlwork.com)
 
Hi Mick,

param = cmd.Parameters.Add("@Retval", SqlDbType.Int)
param.Direction = ParameterDirection.ReturnValue

Hope this helps
 
Back
Top