By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,292 Members | 1,354 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,292 IT Pros & Developers. It's quick & easy.

Getting Return Value From Stored Proceedure.

P: n/a
Hi Everyone,

I am stumped here. I have the following stored proceedure:P

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
Jun 20 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You have to set the parameter direction to ParameterDirection.ReturnValue
explicitly for the return parameter..

"Mick Walker" wrote:
Hi Everyone,

I am stumped here. I have the following stored proceedure:P

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
Jun 20 '07 #2

P: n/a
Hi Everyone,
>
I am stumped here. I have the following stored proceedure:P

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.
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

Jun 20 '07 #3

P: n/a
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)

Mick Walker wrote:
Hi Everyone,

I am stumped here. I have the following stored proceedure:P

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
Jun 20 '07 #4

P: n/a

"Mick Walker" <Mi*********@privacy.netwrote in message
news:5d*************@mid.individual.net...
Hi Everyone,

I am stumped here. I have the following stored proceedure:P

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

Jun 20 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.