Return 0 or 1 in SP if any data exists

[mmitchell]

I have the following sp that doesne't seem to output anything.

ALTER PROCEDURE dbo.spKitsWithSubOrders
 (@lngWOID int, @lngWOIDSub smallint, @bResult bit OUTPUT) 
AS 
SET @bResult = 0 

IF EXISTS
 (
      SELECT     dbo.T_SetupSheetHistoryCombinationsDetail.WOID, dbo.T_SetupSheetHistoryCombinationsDetail.WOIDSub 
      FROM  dbo.T_SetupSheetHistoryCombinationsDetail INNER JOIN
      dbo.T_SetupSheetKitMaster ON 
      dbo.T_SetupSheetHistoryCombinationsDetail.ComboCustPartNum = dbo.T_SetupSheetKitMaster.KitItemPartNum
 ) 
SET @bResult = 1

 RETURN

If I run the select on its own, it returns data, but when I run the sp, the sp does not return 0 or 1.

Also,

How do I get that returned value using ADO?

I tried:

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim lngWOID As Long
Dim lngWOIDSub As Long
Dim bOutput As Boolean

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

lngWOID = 100030
lngWOIDSub = 0

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "spKitsWithSubOrders"
.CommandType = adCmdStoredProc
End With

Set prm = cmd.CreateParameter("@lngWOID", adInteger, adParamInput, , lngWOID)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@lngWOIDSub", adInteger, adParamInput, , lngWOIDSub)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@bResult", adBoolean, adParamOutput, , bOutput)
cmd.Parameters.Append prm

Set rs = cmd.Execute

'::: Now use value of bOutput for results
MsgBox "The returned value is: " & rs.Fields(0)

 

[pono1]

Mitch,

Keep the SP simple, using it to return a recordset to your front-end where the analysis takes place...

Create PROCEDURE ProcMySimpleProcedure

--Create input parameters...
   @MyParameter1 int,
   @MyParameter2 varchar(30)

AS
   SELECT ThisField, ThatField, AndThisField
   FROM dbo.ThisTable
   WHERE ThisField = @MyParameter1 And ThatField = @MyParameter2

In the front-end...

'Open an ADO recordset with a command object...

   Dim MyCmd      As New ADODB.Command
   Dim MyParm     As New ADODB.Parameter
   Dim RST          As New ADODB.Recordset
   Dim MyCount    As Long

'Make a phony connection to the b-e for the example's sake...      
   EstablishConnection 1,2,3
      
'Configure the command object.
   With MyCmd
      .ActiveConnection = MyConn 'The phony connection object.
      .CommandText = "ProcMySimpleProcedure" 'The SP Name.
      .CommandType = adCmdStoredProc
   End With

'Add a parameter to the command object.
   With MyParm
      .Type = adInteger
      .Direction = adParamInput
      .Value = 1
   End With
   MyCmd.Parameters.Append MyParm
   
'Add another parameter to the command object.
   Set MyParm = New ADODB.Parameter
   With MyParm
      .Type = adVarChar
      .Direction = adParamInput
      .Size = 30
      .Value = "Hello"
   End With
   MyCmd.Parameters.Append MyParm
   
'Open a recordset based on the command obj and SP.
   With RST
      .LockType = adLockReadOnly
      .CursorType = adOpenKeyset
      .Open MyCmd
   End With
   
'Any recs returned?
   MyCount = RST.RecordCount
'Show me...
   MsgBox MyCount
   
'Free RAM.
   RST.Close
   Set RST = Nothing
   Set MyCmd = Nothing
   Set MyParm = Nothing

Good book: Chipman and Baron's Access Dev.'s Guide to SQL Server.

Regards,
Tim