Getting Return Value From Stored Proceedure.

[Mick Walker]

Hi Everyone,

I am stumped here. I have the following stored proceedure

CREATE PROCEDURE [dbo].[ImportLinesProductExists]
@SupplierSKU varchar(50),
@RetVal int
AS
Select @Retval = count(*) from dbo.ImportLines
Where [SupplierSKUCode] = @SupplierSKU
if @Retval > 0
BEGIN
Return 0
END
-- It wasn't found so we can now return -1
Return -1

Which works perfectly when I execute it with SQL Server Management Studio.

I call the stored proceedure with the following code:

Public Sub CheckProduct(ByVal _ConnString As String, ByVal
supplierSKUCode as Integer)
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"

With cmd.Parameters
.AddWithValue("@SupplierSKU", supplierSKUCode)
.AddWithValue("@RetVal", System.DBNull.Value)
End With
Try
conn.Open()
ReturnValue = cmd.ExecuteScalar()
Catch ex As SqlException
Throw ex
Finally
conn.Close()
cmd.Parameters.Clear()
End Try
If ReturnValue = 0 Then
InsertTempProducts(_ConnString, Item)
End If
End Sub

But no matter what, the sub always returns 0, even when I manually add a
entry which should conflict, into the database.

Does anyone know whay this is happening?

Kind Regards
Mick Walker

 

[Guest]

You have to set the parameter direction to ParameterDirection.ReturnValue
explicitly for the return parameter..

 

[Hans Kesting]

Hi Everyone,

I am stumped here. I have the following stored proceedure

CREATE PROCEDURE [dbo].[ImportLinesProductExists]
@SupplierSKU varchar(50),
@RetVal int
AS
Select @Retval = count(*) from dbo.ImportLines
Where [SupplierSKUCode] = @SupplierSKU
if @Retval > 0
BEGIN
Return 0
END
Which works perfectly when I execute it with SQL Server Management
Studio.

I call the stored proceedure with the following code:

Public Sub CheckProduct(ByVal _ConnString As String, ByVal
supplierSKUCode as Integer)
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"
With cmd.Parameters
.AddWithValue("@SupplierSKU", supplierSKUCode)
.AddWithValue("@RetVal", System.DBNull.Value)

as mentioned before, you need to set the Direction to ParameterDirection.ReturnValue

End With
Try
conn.Open()
ReturnValue = cmd.ExecuteScalar()

you don't need ExecuteScalar here: that will return the first value in the
first column
in the first table returned, and you don't return any tables at all.
An ExecuteNonQuery should do.

Catch ex As SqlException
Throw ex
Finally
conn.Close()
cmd.Parameters.Clear()

before you clear the parameters, read the value of @RetVal - this is the
value you want.

 

[bruce barker]

you are confused.

executescaler returns the first column value of the first row of the
first resultset. your proc does not return a resultset, so it should be
throwing an error. you should be calling ExecuteNonquery().

after processing the resultsets (or call ExecuteNonquery which does
this) you can access output parameters (though you need to set the
parameter direction to output before making the query).

to get the actual return value of a proc, you add an int parameter with
the direction of ReturnValue. after all resultsets have been returned,
you can accesses this parameter's value to get the return value.


-- bruce (sqlwork.com)

 

[Lloyd Sheen]

Hi Everyone,

I am stumped here. I have the following stored proceedure

CREATE PROCEDURE [dbo].[ImportLinesProductExists]
@SupplierSKU varchar(50),
@RetVal int
AS
Select @Retval = count(*) from dbo.ImportLines
Where [SupplierSKUCode] = @SupplierSKU
if @Retval > 0
BEGIN
Return 0
END
-- It wasn't found so we can now return -1
Return -1

Which works perfectly when I execute it with SQL Server Management Studio.

I call the stored proceedure with the following code:

Public Sub CheckProduct(ByVal _ConnString As String, ByVal
supplierSKUCode as Integer)
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"

With cmd.Parameters
.AddWithValue("@SupplierSKU", supplierSKUCode)
.AddWithValue("@RetVal", System.DBNull.Value)
End With
Try
conn.Open()
ReturnValue = cmd.ExecuteScalar()
Catch ex As SqlException
Throw ex
Finally
conn.Close()
cmd.Parameters.Clear()
End Try
If ReturnValue = 0 Then
InsertTempProducts(_ConnString, Item)
End If
End Sub

But no matter what, the sub always returns 0, even when I manually add a
entry which should conflict, into the database.

Does anyone know whay this is happening?

Kind Regards
Mick Walker

This looks like a function rather than a procedure. Make this a Stored
Function and return the count as the return value for the function.

LS