I have a stored procedure that outputs 341 rows if using the syntax:

SELECT * FROM procedurename
(<date1>,<date2>,<integer1>,integer2>,<integer3>,< integer4>,<integer5>)

and 722 rows if using the syntax:

CALL procedurename
(<date1>,<date2>,<integer1>,integer2>,<integer3>,< integer4>,<integer5>)

Aren't the two supposed to give the same results?

I am running ASA 9.02.3169.

Here is the call:
CALL "SOS"."S_ProvAgeSex"('2000-01-01', '2009-12-31', 0, 12, 19, 30,
60)

And here is the procedure:
CREATE PROCEDURE S_ProvAgeSex
(IN
@IntakeDateFROM DATE,
@IntakeDateTO DATE,
@AgeRange1Start INTEGER,
@AgeRange2Start INTEGER,
@AgeRange3Start INTEGER,
@AgeRange4Start INTEGER,
@AgeRange5Start INTEGER
)

BEGIN
DECLARE AgeRange1End INTEGER;
DECLARE AgeRange2End INTEGER;
DECLARE AgeRange3End INTEGER;
DECLARE AgeRange4End INTEGER;

SET AgeRange1End = (@AgeRange2Start - 1);
SET AgeRange2End = (@AgeRange3Start - 1);
SET AgeRange3End = (@AgeRange4Start - 1);
SET AgeRange4End = (@AgeRange5Start - 1);

SELECT
(UPPER(providers.provlname)+', '+providers.provfname) AS "Provider",
"Patients"."ID",
(CASE
WHEN patients.sex = 'F' THEN 'Female'
WHEN patients.sex = 'M' THEN 'Male'
ELSE 'Unknown'
END) AS "Sex",
(CASE
WHEN AgeInYears(patients.dob,today()) BETWEEN @AgeRange1Start AND
AgeRange1End THEN STRING(@AgeRange1Start)+'-'+STRING(AgeRange1End)
WHEN AgeInYears(patients.dob,today()) BETWEEN @AgeRange2Start AND
AgeRange2End THEN STRING(@AgeRange2Start)+'-'+STRING(AgeRange2End)
WHEN AgeInYears(patients.dob,today()) BETWEEN @AgeRange3Start AND
AgeRange3End THEN STRING(@AgeRange3Start)+'-'+STRING(AgeRange3End)
WHEN AgeInYears(patients.dob,today()) BETWEEN @AgeRange4Start AND
AgeRange4End THEN STRING(@AgeRange4Start)+'-'+STRING(AgeRange4End)
WHEN AgeInYears(patients.dob,today()) > AgeRange4End THEN STRING
(AgeRange4End+1)+'+'
END) AS "Age",
(CASE
WHEN COUNT(journal.jnum) <= 3 THEN ' 3 or fewer'
WHEN COUNT(journal.jnum) BETWEEN 4 AND 9 THEN ' 4 - 9 visits'
ELSE '10 plus visits'
END) AS "Visits",
@IntakeDateFrom AS "IntakeDateFrom",
@IntakeDateTo AS "IntakeDateTo"

FROM
((("SOS"."Journal" "Journal"
INNER JOIN "SOS"."JCharges" "JCharges" ON
"Journal"."JNum"="JCharges"."JNum")
INNER JOIN "SOS"."Patients" "Patients" ON
"Journal"."PtNum"="Patients"."PtNum")
INNER JOIN "SOS"."Services" "Services" ON
"JCharges"."ServiceNum"="Services"."ServiceNum ")
INNER JOIN "SOS"."Providers" "Providers" ON
"JCharges"."ProviderNum"="Providers"."ProviderNum"
LEFT OUTER JOIN "SOS"."ProvType" "ProvType" ON
"Providers"."ProvTypeNum" = "Providers"."ProvTypeNum"
WHERE
patients.licnum=101 AND
patients.dob IS NOT NULL AND
(patients.intakedate >=@IntakeDateFrom AND patients.intakedate <=
@IntakeDateTo) AND
services.cptcode IN ('90801','90806','90846','90847','90853') AND
providers.hiderow = 0 AND
COALESCE(provtype.provtypecode,' ') <> 'MD'
GROUP BY
"Provider",patients.id, "sex","age";
END;

[I can't imagine that it is relevant, but just in case, the function
AGEINYEARS used in the procedure is as follows]

CREATE FUNCTION AGEINYEARS( IN BIRTHDAY DATE, REFDATE DATE )
RETURNS INTEGER
//THIS FUNCTION RETURNS THE AGE FOR PASSED BIRTHDAY AND REFERENCE DATE
BEGIN
DECLARE VAGE INTEGER;
SET VAGE = YEARS( BIRTHDAY, REFDATE ) -
(IF (MONTH( BIRTHDAY ) * 100 + DAY( BIRTHDAY )) > (MONTH(REFDATE)
* 100 + DAY(REFDATE))
THEN 1
ELSE 0
ENDIF) ;
RETURN VAGE;
END


- Perplexed in Florida
(Seth Krieger)

NEVER MIND! Found an error in one of the joins. All better!


On Nov 13, 5:50*pm, Seth <sethkrie... (AT) yahoo (DOT) com> wrote: