Re: Getting Return Value From Stored Proccedure (Part 2)

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)

Mick Walker wrote:
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
.

Relevant Pages

  • Output Parameter always 0 when used with a dataadapter
    ... I have a stored procedure that has an output parameter defined as output and ... value in the bottom of the proc to be sure it had something other than 0. ... Dim oDs As New DataSet ... ' @lihhstcdfk INT, ...
    (microsoft.public.dotnet.framework.adonet)
  • problem using ADO command to run output stored procedure
    ... declare @c int ... param As New ADODB.Parameter ... Dim j As Integer ...
    (microsoft.public.vb.general.discussion)
  • Re: FTP
    ... Dim dwFileAttributes As Integer ... Public cAlternate As String ... > public int dwHighDateTime; ... > public static extern IntPtr InternetConnect( ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Larkin, Power BASIC cannot be THAT good:
    ... #COMPILE EXE ... DIM AAS INTEGER ' INPUT ADC SAMPLES ... unsigned int *sum_data; ... printf; ...
    (sci.electronics.design)
  • Re: Larkin, Power BASIC cannot be THAT good:
    ... DIM AAS INTEGER ' INPUT ADC SAMPLES ... unsigned int *sum_data; ... printf; ... Re: My program is prettier. ...
    (sci.electronics.design)
Quantcast