Bug in SQL Server 2000 with OPENXML?
Hi,
I am trying to make some stored procedures that take in lists of well sites and well ids and find the wells in the sites except for those in the list of well ids.
I have a stored procedure like this.
ALTER procedure sp_wellSelect (@criteria ntext)
as
DECLARE @hDoc int
--Prepare input values as an XML documnet
exec sp_xml_preparedocument @hDoc OUTPUT, @criteria
--Select data from the table based on values in XML
SELECT distinct w.well_id, w.well_name
FROM Wells_by_site ws
INNER JOIN well w
ON ws.well_id = w.well_id
WHERE ws.SITE_ID IN (
SELECT sid
FROM OPENXML (@hdoc, '/D/C/S', 1)
WITH (sid int)
) AND
w.WELL_ID NOT IN (
SELECT wid
FROM OPENXML (@hdoc, '/D/C/W', 1)
WITH (wid varchar(15))
)
ORDER BY w.well_name
EXEC sp_xml_removedocument @hDoc
GO
When I run this though it DOES NOT exclude the wells in the list of well ids. Here is an example.
-- Get all wells in site 80 except for 'A7793'
sp_wellSelect '<D><C><S sid="80"/><W wid="A7793"/></C></D>'
well_id well_name
A7793 299-W21-51
A7858 299-W22-51
B8552 299-W22-79
A7833 299-W22-8
A9901 699-36-70A
(5 row(s) affected)
I can run a query which is equivalent to the stored procedure and this one will exclude the well as expected.
SELECT distinct w.well_id, w.well_name
FROM Wells_by_site ws
INNER JOIN well w
ON ws.well_id = w.well_id
WHERE ws.site_id IN (80)
AND
w.well_id NOT IN ('A7793')
ORDER BY w.well_name
well_id well_name
A7858 299-W22-51
B8552 299-W22-79
A7833 299-W22-8
A9901 699-36-70A
(4 row(s) affected)
Another strange thing is that if I don't do a SELECT DISTINCT in the stored procedure, it does exclude the 'A7793'
I have just removed the DISTINCT and saved the stored procedure. Now I run it again.
sp_wellSelect '<D><C><S sid="80"/><W wid="A7793"/></C></D>'
well_id well_name
A7858 299-W22-51
B8552 299-W22-79
A7833 299-W22-8
A9901 699-36-70A
(4 row(s) affected)
How can the same query give different results? Could this be a bug in SQL server with OPENXML? It makes no sense to me?
Thanks in advance
