Returning a parameter from Stored Procedure 1

[RobHVB6Sql]

(Using VB6 and SQL Server 7.0)

The stored procedure
--------------------
CREATE PROCEDURE spFamilyArchiveNeeded

(
@FamilyNo VARCHAR (8),
@NoToChangeDel INTEGER,
@retNeedsArchive INTEGER = NULL OUTPUT
)

AS

SELECT PersonID
FROM tblPerson
WHERE FamilyNo LIKE @FamilyNo

/* check if the change will remove the family number */
IF @@RowCount = @NoToChangeDel
BEGIN
SET @retNeedsArchive = 1
RETURN @retNeedsArchive
END
ELSE
BEGIN
SET @retNeedsArchive = 0
RETURN @retNeedsArchive
END
GO
===========================
Problem, what is the VB code to call and return this?

I would normally call this SP like this:
TheSQL = "EXEC spFamilyArchiveNeeded 'TEST005', 1, NULL"
Set adors = adoEnigmaConn.Execute(TheSQL)

Thanks in advance. Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[mkuan]

Dim oConn as ADODB.Connection
Dim oCom as ADODB.Command
Dim oParam1 as ADODB.Parameter
Dim oParam2 as ADODB.Parameter
Dim oParam3 as ADODB.Parameter
Dim str, int1 as String

Set oConn = New ADODB.Connection
oConn.Open "Provider...blah, blah, blah.."

Set oCom = New ADODB.Command
Set oCom.ActiveConnection = oConn
oCom.CommandText = "yoursprocname"
oCom.CommandType = adCmdStoredProc

str = strFamilyNo
int1 = intNoToChangedDel

Set oParam1 = oComm.CreateParameter("FamilyNo", adVarChar, adParameter, 8, str)
oComm.Parameters.Append oParam1
Set oParam2 = oComm.CreateParameter("NoToChangeDel", adInteger, adParameter, 3, int1)
oComm.Parameters.Append oParam2
Set oParam3 = oComm.CreateParameter("retNeedsArchive", adInteger, adParameterOutput, 3)
oComm.Parameters.Append oParam3

oCom.Execute

' to see the output
Print oParam3

' Clean up by setting all objects to nothing.

' check out parameter object for details.