Odd query result in SQL server 2000

Hi,
I am running SQL Server 2000, SP4.
I have stored procedure with a query like this:
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (...list of IDs)
AND PE.EDU_ID=1
By running the stored procedure this query gives the result 221, -1
This result is wrong.
I run EXACTLY the same query using query analyzer and that gives the
result 199, -1
Which is correct
How is this possible? I am running the both the SP and the query under
the same account.
I have tried to drop the SP and re-create it. I have tried to re-
compile the SP. Nothing seems to help.
Any ideas?
Thanks.
Best regards,
EirikUse the Show Actual Query Plan feature to see what both queries are doing.
Also, is the list of IDs a parameter in the sproc?
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Eiriken" <eirik@.oslo.online.no> wrote in message
news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
> Hi,
> I am running SQL Server 2000, SP4.
> I have stored procedure with a query like this:
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (...list of IDs)
> AND PE.EDU_ID=1
> By running the stored procedure this query gives the result 221, -1
> This result is wrong.
> I run EXACTLY the same query using query analyzer and that gives the
> result 199, -1
> Which is correct
> How is this possible? I am running the both the SP and the query under
> the same account.
> I have tried to drop the SP and re-create it. I have tried to re-
> compile the SP. Nothing seems to help.
> Any ideas?
> Thanks.
> Best regards,
> Eirik|||Thank you for answering
The list of IDs is from a temporary table created in the same SP.
Looking at the execution plan shows that indeed there is a difference
between running the queries in the SP and in the Query Analyzer. The
"messages" tab also shows a difference. By running the queries in
query analyzer shows "23 rows affected and 1 rows affected". Running
the queries in a SP executed in query analyzer shows "23 rows
affected, 5 rows affected and 1 rows affected". Where the 5 rows come
from I don't know.
I have stripped down the queries and I am pasting the query here.
After stripping the result is the still wrong as earlier.
CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
NumberFullTime int, NumberPartTime int, NumberTotal int)
INSERT INTO #Tmp_Countries
SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
(5,69,149,8,6,88,2,7,79,89,80,34,83,82,8
5,65,71, 73,86,87,1,68,190)
SELECT COUNT(*), -1
FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
PM.PER_ID=PU.PER_ID
LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
CountryId>=0)
AND PE.EDU_ID=1
DROP TABLE #Tmp_Countries
On 24 Nov, 17:39, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Use the Show Actual Query Plan feature to see what both queries are doing.
> Also, is the list of IDs a parameter in the sproc?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Eiriken" <ei...@.oslo.online.no> wrote in message
> news:d20f3c54-698c-42e8-a5cd-9406623f26e7@.i29g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>|||Eiriken <eirik@.oslo.online.no> wrote in
news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:

> Thank you for answering
> The list of IDs is from a temporary table created in the same SP.
> Looking at the execution plan shows that indeed there is a difference
> between running the queries in the SP and in the Query Analyzer. The
> "messages" tab also shows a difference. By running the queries in
> query analyzer shows "23 rows affected and 1 rows affected". Running
> the queries in a SP executed in query analyzer shows "23 rows
> affected, 5 rows affected and 1 rows affected". Where the 5 rows come
> from I don't know.
> I have stripped down the queries and I am pasting the query here.
> After stripping the result is the still wrong as earlier.
> CREATE TABLE #Tmp_Countries (CountryId int, CountryName varchar(200),
> NumberFullTime int, NumberPartTime int, NumberTotal int)
> INSERT INTO #Tmp_Countries
> SELECT CNT_ID, CNT_NAME, 0, 0, 0 FROM cnt_country WHERE CNT_ID IN
> (5,69,149,8,6,88,2,7,79,89,80,34,83,82,8
5,65,71, 73,86,87,1,68,190)
> SELECT COUNT(*), -1
> FROM PERSON_MEMBERSHIP PM LEFT OUTER JOIN PERSON_UNIVERSITY PU ON
> PM.PER_ID=PU.PER_ID
> LEFT OUTER JOIN PERSON_EDUCATIONTYPE PE ON PM.PER_ID=PE.PER_ID
> WHERE PM.ORG_ID=1 AND PM.PER_MEM_ACTIVE=1
> AND PU.CNT_ID NOT IN (SELECT CountryId FROM #Tmp_Countries WHERE
> CountryId>=0)
> AND PE.EDU_ID=1
> DROP TABLE #Tmp_Countries
Possibly the problem is with one or more PU/PE columns in the WHERE clause
being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the whole
of the WHERE clause will evaluate to NULL.
Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
HTH|||NULLs are my first guess for this too. They could cause problems because
settings are different between direct execution and the sproc settings'
execution context.
Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
it's owner/schema. Not only is this more efficient - there are scenarios
where it can lead to the wrong results too.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns99F2D9D92765DChrisCheneytesconet
@.80.5.182.99...
> Eiriken <eirik@.oslo.online.no> wrote in
> news:3db16b9f-44cc-45ac-86b5-51823cc0c1c2@.o42g2000hsc.googlegroups.com:
>
> Possibly the problem is with one or more PU/PE columns in the WHERE clause
> being NULL (as a result of the OUTER JOIN(s)) and, in such cases, the
> whole
> of the WHERE clause will evaluate to NULL.
> Use IS NULL/IS NOT NULL/ISNULL/COALESCE to deal with such cases.
> HTH|||I started investigating the case more carefully and compared the
result to see what was really the difference.
The answer was exactly as you proposed. The settings were indeed
different and the NULLs were the reason for the different result.
Thank you Kevin and Chris.
On 25 Nov, 00:05, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> NULLs are my first guess for this too. They could cause problems because
> settings are different between direct execution and the sproc settings'
> execution context.
> Another minor point - ALWAYS prefix EVERY object (even temporary ones) by
> it's owner/schema. Not only is this more efficient - there are scenarios
> where it can lead to the wrong results too.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "Chris.Cheney" <Chris.CheneyXXNOSPA...@.tesco.net> wrote in message
> news:Xns99F2D9D92765DChrisCheneytesconet
@.80.5.182.99...
>
>
>
>
>
>
>
>
>
>sql