Retrieving return value from a SQL Server Stored Procedure using VB 2

[celly]

Hi,

I'm having a problem retrieving the return value in Visual Basic from a SQL Server stored procedure. Can anyone help by giving me a small example of the code needed to retrieve the value in VB and a simple SQL Server Stored Procedure that produces the return value.

Thanks

 

[BBaggins]

You didn't specify what kind of problem you're having or what stored procedure you're executing, so this is just generic advice.

There's nothing special about pulling data back from a stored procedure - remember that a stored procedure is just a database query that's been named and stored on the server.
You execute a stored procedure from VB code the same way you'd execute a query, and process the result set(s) the same way.

There can be complications, however. Stored procedures are sometimes intended for interactive rather than programmatic use. Those SP's may return column headings and other formatting meant to make the results more readable when executed by hand via ISQL/W or similar tool.

In those cases, you have to treat each line of text as a record in the result set and discard the extraneous text that your program doesn't need.

Bring up ISQL/W and execute the SP manually. This will give you a clue as to how you'll have to process the results in code. Look for blank lines or leading blanks that need to be discarded.

 

[MarkSweetland]

Here's a very simplified example:

/* -----------------------------------
Returns the Fiscal Yr of input_date (SQL Server 7.0)
-----------------------------------*/
CREATE PROCEDURE dbo.sp_get_fy
@input_date datetime, @fy smallint output
AS
SELECT @fy = datepart(year, @input_date) + (datepart(month,@input_date) / 10)
/* -------------------------------------- */

The VB use (ADO):

Dim cmd As New ADODB.Command
Dim fy As Integer
.
.
.
With cmd
.ActiveConnection = 'Your connection info here
.CommandTimeout = 0
.CommandType = adCmdStoredProc
.CommandText = "sp_get_fy"
.Parameters.Refresh
.Parameters(1).Value = "6/7/01" ' @input_date
.Parameters(2).Direction = adParamOutput ' @FY
.Parameters(2).Type = adInteger
.Execute
'Stored proc returns fiscal year
fy = .Parameters(2).Value
End With
MsgBox fy
.
.
.

There are other ways to create the parameters of the command object but this will get you started.


Mark

 

[celly]

Thanks for the advice and the small bit of code. That code was all I needed to get started in the right direction