query returns incorrect results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

query returns incorrect results

Author

Topic

mcp111
Starting Member

44 Posts

Posted - 2004-08-20 : 10:11:49

I am trying to find stored procedures written by me which have either 'gross' or 'inpatient' in them
Here's my query

select distinct so.id,so.name from sysobjects so
join syscomments sc
on so.id=sc.id
and so.name
like '%up_rpt%' - all procs start with up_rpt
and sc.text like '%Partha%'
and ( sc.text like '%inpat%'
or sc.text like '%gross%')

Here are the results

id name
----------- --------------------------------------------------------------------------------------------------------------------------------
1348199853 up_rpt_ERServicesByRateCodeGroup
1508200423 up_rpt_InpatientHospitalServicesLTC
1700201107 up_rpt_PrelimPaymentSummary
342292279 up_rpt_SterilandHysterPayments
1796201449 up_rpt_Title19DualEligibleforFFP
1828201563 up_rpt_Title5NotDualEligible

(6 row(s) affected)

But there is at least one more stored procedure that I know, up_rpt_BillingSummary that is not listed here but contains the string 'inpatient'.

Then why doesn't the query work properly?
I think this is a bug in SQL Server.
Now I'll have to manually check all my procedures to identify the correct ones!!!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-20 : 10:15:54

It may be that impatient lines up with the split for seperating the procedure into its stored records.

record1: ...blah blah imp
record2: atient blah blah...

Corey

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-20 : 10:20:15

Maybe it doesn't match -

and sc.text like '%Partha% ?

-------
Moo. :)

mcp111
Starting Member

44 Posts

Posted - 2004-08-20 : 10:34:04

I don't understand - please clarify.
The entire procedure code will be in the text column in the syscomments table. So why can't it find the string 'inpatient' when it is there in the procedure code.

quote:
Originally posted by Seventhnight

It may be that impatient lines up with the split for seperating the procedure into its stored records.

record1: ...blah blah imp
record2: atient blah blah...

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-20 : 11:09:10

The entire procedure may or may not in the text column of the syscomments table. Sql will split it up to a few rows if it is a long procedure.

try the following:
Select text From syscomments where id = (Select id from sysobjects where name='up_rpt_BillingSummary')

Corey

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-20 : 11:20:13

Follow what Corey says first, but what I meant was, regardless of inpatient, you have another AND condition that is

and sc.text like '%Partha%'

that may not be in that proc you are talking about.

-------
Moo. :)

mcp111
Starting Member

44 Posts

Posted - 2004-08-20 : 11:22:37

I tried it

The string 'INPATIENT ' is contained in one row. Then why doesn't SQL find it. The entire procedure is in 8 rows.

mcp111
Starting Member

44 Posts

Posted - 2004-08-20 : 11:26:09

I have checked that all the strings are present in the procedure.

quote:
Originally posted by mr_mist

Follow what Corey says first, but what I meant was, regardless of inpatient, you have another AND condition that is

and sc.text like '%Partha%'

that may not be in that proc you are talking about.

-------
Moo. :)

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-20 : 11:37:44

Maybe the collation is case-sensitive?

-------
Moo. :)

mcp111
Starting Member

44 Posts

Posted - 2004-08-20 : 15:38:06

no it is not.

quote:
Originally posted by mr_mist

Maybe the collation is case-sensitive?

-------
Moo. :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-20 : 15:53:52

The easiest way to figure this out is step through the conditions a determine which one is ruling out the 'up_rpt_BillingSummary'.

Run these in order:

quote:

select distinct so.id,so.name from sysobjects so
join syscomments sc
on so.id=sc.id
Where so.name = 'up_rpt_BillingSummary'

quote:

select distinct so.id,so.name from sysobjects so
join syscomments sc
on so.id=sc.id
Where so.name = 'up_rpt_BillingSummary'
and so.name like '%up_rpt%' - all procs start with up_rpt

quote:

select distinct so.id,so.name from sysobjects so
join syscomments sc
on so.id=sc.id
Where so.name = 'up_rpt_BillingSummary'
and so.name like '%up_rpt%' - all procs start with up_rpt
and sc.text like '%Partha%'

quote:

select distinct so.id,so.name from sysobjects so
join syscomments sc
on so.id=sc.id
Where so.name = 'up_rpt_BillingSummary'
and so.name like '%up_rpt%' - all procs start with up_rpt
and sc.text like '%Partha%'
and sc.text like '%inpat%'

quote:

select distinct so.id,so.name from sysobjects so
join syscomments sc
on so.id=sc.id
Where so.name = 'up_rpt_BillingSummary'
and so.name like '%up_rpt%' - all procs start with up_rpt
and sc.text like '%Partha%'
and (sc.text like '%inpat%'
or sc.text like '%gross%')

It should help identify where the problem is.

Corey

mcp111
Starting Member

44 Posts

Posted - 2004-08-20 : 16:45:21

This query fails

select distinct so.id,so.name from sysobjects so
join syscomments sc
on so.id=sc.id
Where so.name = 'up_rpt_BillingSummary'
and so.name like '%up_rpt%' - all procs start with up_rpt
and sc.text like '%Partha%'
and sc.text like '%inpat%'

the proc 'up_rpt_BillingSummary' has the both strings 'inpatient' and 'partha' but sql doesn't identify it!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-20 : 16:52:00

If you want to search objects for a string, you can use this:

http://vyaskn.tripod.com/code/search_stored_procedure_code.txt

Tara

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-20 : 16:58:25

Oh wait, I know!!

quote:


Select
	id,
	name
From
	(
	select distinct so.id,so.name from sysobjects so 
	join syscomments sc 
	on so.id=sc.id 
	Where so.name like '%up_rpt%' - all procs start with up_rpt 
	and sc.text like '%Partha%' 
	Union All
	select distinct so.id,so.name from sysobjects so 
	join syscomments sc 
	on so.id=sc.id 
	Where so.name like '%up_rpt%' - all procs start with up_rpt 
	and (sc.text like '%inpat%' or sc.text like '%gross%')
	) as a
Group By id, name
Having count(*)=2

While the procedure may have both 'partha' and 'inpatient', they may not be found in the same record.

Corey

mcp111
Starting Member

44 Posts

Posted - 2004-08-20 : 17:06:33

This procedure can only search for 1 string. I have 3 search conditions.

quote:
Originally posted by tduggan

If you want to search objects for a string, you can use this:

http://vyaskn.tripod.com/code/search_stored_procedure_code.txt

Tara

mcp111
Starting Member

44 Posts

Posted - 2004-08-20 : 17:07:49

Thanks - that worked!

quote:

Originally posted by Seventhnight

Oh wait, I know!!

quote:


Select
	id,
	name
From
	(
	select distinct so.id,so.name from sysobjects so 
	join syscomments sc 
	on so.id=sc.id 
	Where so.name like '%up_rpt%' - all procs start with up_rpt 
	and sc.text like '%Partha%' 
	Union All
	select distinct so.id,so.name from sysobjects so 
	join syscomments sc 
	on so.id=sc.id 
	Where so.name like '%up_rpt%' - all procs start with up_rpt 
	and (sc.text like '%inpat%' or sc.text like '%gross%')
	) as a
Group By id, name
Having count(*)=2

While the procedure may have both 'partha' and 'inpatient', they may not be found in the same record.

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-20 : 17:26:12

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources