Return Values from Stored Procedures

[brannon]

I need to return values from a stored procedure using SQL Server 2000 to a VB6 program. The stored procedure will calculate the values but I can't get it to return the values to the program. Thanks in advance for any help given.

 

[jbenson001]

does the SP retun the value correctly if your run it in QA? or is it a problem on the front end code?

 

[ClayG]

Are you returning you 'values' in a recordset or some other way (output variables?). Details please.

Otherwise, for a wild guess, try
Set NoCount On
in the procedure (long story)

 

[scking]

I frequently use a stored procedure to return a value which is then assigned to a variable in VB. The sp needs to use the OUTPUT param and assign a value and the VB proc needs to use ADODB. Here is a sample that will fill the variable lngHolidays with 8 and return 252 for the number of work days in 2005 using the GetWorkDays(#1/1/2005#, #12/31/2005#).

ALTER PROCEDURE spGetHolidayCount
(
@Start datetime,
@End datetime,
@HolidayCount int OUTPUT
)
AS
SELECT @HolidayCount=Count(*)
FROM tblHolidays
WHERE HolidayDate BETWEEN @Start AND @End

RETURN

Public Function GetWorkDays(StartDate As Date, EndDate As Date) As Double

' This function calculates the number of workdays between StartDate and EndDate
' The work week is defined as Monday through Friday.
' The function does not exclude holidays.

Dim pcnnDb As New ADODB.Connection
Dim oCmd As ADODB.Command
Dim oParm1 As ADODB.Parameter
Dim oParm2 As ADODB.Parameter
Dim oParm3 As ADODB.Parameter
Dim lngHolidays As Long
Dim lngWorkDays As Long
Dim dtDate As Date, lngWorkDays As Long

On Error GoTo HandleErr

For dtDate = StartDate To EndDate
If (Weekday(dtDate, 1) <> 1) And (Weekday(dtDate, 1) <> 7) Then
lngWorkDays = lngWorkDays + 1
End If
Next dtDate

Set oCmd = New ADODB.Command
Set oParm1 = New ADODB.Parameter
Set oParm2 = New ADODB.Parameter
Set oParm3 = New ADODB.Parameter

If pcnnDb Is Nothing Or Len(pcnnDb.ConnectionString) = 0 Then
Set pcnnDb = CurrentProject.Connection
End If

With oCmd
.CommandText = "spGetHolidayCount"
.CommandType = adCmdStoredProc
.ActiveConnection = pcnnDb
oParm1.Direction = adParamInput
oParm1.Name = "@Start"
oParm1.Type = adDate
oParm1.Value = StartDate
.Parameters.Append oParm1

oParm2.Direction = adParamInput
oParm2.Name = "@End"
oParm2.Type = adDate
oParm2.Value = EndDate
.Parameters.Append oParm2
oParm3.Direction = adParamOutput

oParm3.Name = "@HolidayCount"
oParm3.Type = adInteger
oParm3.Value = Null
.Parameters.Append oParm3
.Execute
End With

lngHolidays = oParm3.Value
lngWorkDays = lngWorkDays - lngHolidays

GetWorkDays = lngWorkDays

Exit_Proc:
Exit Function

HandleErr:
Select Case Err.Number
Case Else
Call ErrorLog("basSchedule_GetWorkDays", Err) 'ErrorHandler:$$N=Form_frmLruMain.cmdLoad_Click
End Select
Resume Exit_Proc
Resume

End Function

---------------------
scking@arinc.com
---------------------