How to get a return value from a stored procedure in VB.NET

Question

Dear All,

I have a stored procedure in SQL Server for generating transaction numbers.

Can anyone help me with how to call the Stored Procedure from VB.NET and how will i get the value that is returned from the procedure into the front end.

Regards, George

Answer 1

Old article but very handy:

Retrieving Scalar Data from a Stored Procedure

Answer 2

I think you want something like this:

Public Sub Foo()

Using sql As New SqlClient.SqlConnection("YourConnection")
  sql.Open()
  Using cmd As New SqlClient.SqlCommand("YourSPName", sql)
    cmd.CommandType = CommandType.StoredProcedure
    Dim myReturnValue As String = cmd.ExecuteScalar
  End Using
End Using

End Sub

Where myReturnValue will be what ever your output param in SQL is.

Answer 3

What kind of value is it you're returning? Will that value in turn result in another database action?

It might be best to return data instead of a single value.

For example if you were verifying the username and password for a potential login, instead of returning a simple true or false you would return the users information. No information returned means failed login.

This method has the advantage of minimising database requests, something which will have a serious effect if it is a common action.

Personally I've never needed to return a single value.