Return value from SP

[OldSlowDog]

I have a store procedure (SQL 2000) return a (0) found and (1) not found value. Did I do the sp correctly? How do I check the return value in Access ADP? Thank you very much.

The Store procedure:
ALTER PROCEDURE Chk_ZipCoverage
/* =============================================
Create date: 5/23/2008
Description: Check if we have patients who live in a
given zip code (ie 99023) with a a given insurance
coverage (ie Medicare)

return code = 0, Yes we have
return code = 1, No we don't have
===========================================
*/
(
@AZip varchar(10)
@InsCo varchar (30)
)
AS

SET NOCOUNT ON
DECLARE @Count INT

SELECT @Count = Count(*) From Patient_tbl WHERE (ptZip = @AZip and PtInsCo = @InsCo)
IF @Count > 0
RETURN(0) /* found */
ELSE
RETURN(1) /* not found */
------------

My problem is that I am not getting the return value (either a 1 or 0 ) from the stored procedure in my Access ADP.

The Access code is:
Dim cmd As New ADODB.Command
Dim Param As ADODB.Parameter
Dim spReturn As Integer

With cmd
.ActiveConnection = CurrentProject.Connection
End With

cmd.CommandText = "Chk_ZipCoverage"
cmd.CommandType = adCmdStoredProc

Set Param = New ADODB.Parameter
Param.Name = "ptZip"
Param.Type = adVarChar
Param.Size = 10
Param.Value = "99301"
Param.Direction = adParamInput
cmd.Parameters.Append Param

Set Param = New ADODB.Parameter
Param.Name = "ptIns"
Param.Type = adVarChar
Param.Size = 30
Param.Value = "Cigna"
Param.Direction = adParamInput
cmd.Parameters.Append Param

spReturn = cmd.Execute
If spReturn = 0
Msgbox "Patients found in zip"
else
Msgbox "No patient found in zip"

End Sub

Thank you for helping.

 

[gmmastros]

thread183-1476204

2 days ago, you were advised to NOT use a return value, but to use an output parameter instead. I even posted a link that shows how to capture the results of an output parameter in an Access ADP.

As far as your query is concerned, it can be more efficient. Think about it this way.... do you really care how many there are? No. You just want to know if there is 1 or more.

ALTER      PROCEDURE Chk_ZipCoverage
/* =============================================
  Create date: 5/23/2008
  Description: Check if we have patients who live in a
  given zip code (ie 99023) with a a given insurance
  coverage (ie Medicare)
  
  return code = 0, Yes we have
  return code = 1, No we don't have
   ===========================================
*/
(
@AZip varchar(10)
@InsCo varchar (30)[!],
@PatientExists Bit Output[/!]
)
AS

SET NOCOUNT ON

If [!]Exists[/!](SELECT * From Patient_tbl WHERE (ptZip = @AZip and PtInsCo = @InsCo))
	Set @PatientExists = 1
Else
	Set @PatientExits = 0

------------

Exists will return true for the first row that meets the filter criteria without having to look through the entire table (to calculate a count).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

You should ask that question in Access form.
Ask how to get the returned value from SQL Server SP.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bborissov]

George,
From comments:

return code = 0, Yes we have
return code = 1, No we don't have

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

I minor change to the comments can fix that problem easy enough.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Sure


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[OldSlowDog]

George,
You are right. I interpret the "Output parameter" as the outcome from the query. I think that's what my mistake was.
Now I understand that it means anything that I want as a result of the SP.

It will work for me. Thanks for helping.
Ed

 

[ESquared]

FYI

Return values have to be defined as parameters and added to the ADO parameters collection first, as something like adParamReturn. The Execute method only returns recordsets, not SP return values.

You should really be asking this question in the ADO forum.