SQL Server – Fixing Incorrect Results from Object_name(object_id)

sql servert-sql

We are trying to exclude stored procedures which have name like '%backup%' or %WI%'..

Here is the total query i used..

;WITH CTE
AS
(select object_name(object_id) as procname
from sys.sql_modules
where definition LIKE '%CTLEMPLOYEE%' and ( object_name(object_id) NOT LIKE '%BACKUP%' 
  or object_name(object_id) not like '%backup%'
  OR object_name(object_id) NOT LIKE '%Wi%')
)
SELECT * FROM CTE

sample Output of above query:
report_spBIHRRecruitmentReport_Phase2_backup

But strangely above query is returning results which still have names
like
1.backup
2.Wi

Then i tried to use collate to restrict results like results..

;WITH CTE
AS
(select object_name(object_id) as procname
from sys.sql_modules
where definition LIKE '%CTLEMPLOYEE%' collate SQL_Latin1_General_CP1_CI_AS
 and ( object_name(object_id) NOT LIKE '%BACKUP%' 
  or object_name(object_id) not like '%backup%' 
  OR object_name(object_id) NOT LIKE '%Wi%' collate SQL_Latin1_General_CP1_CI_AS
)) 
SELECT * FROM CTE

Output still contains names like below:

report_spBIHRRecruitmentReport_Phase2_backup

Below is my database info:
Version:SQL Server 2012
Collation:SQL_Latin1_General_CP1_CI_AS
comptabilty level :90

ASK:
Can you please help me understand why i am still getting names like '%backup% or '%wi%'

Scripts to Reproduce:

create proc usp_test1_backup
as
begin
end


create proc usp_test2_wi
as
begin
end

drop proc usp_test1_backup
drop proc usp_test2_wi

Execution plan of my query if it helps you:

http://pastebin.com/aKBqkPzj

Please let me know if you need any further info

Best Answer

The answer is quite simple. You're using a load of ORs, which should be ANDs:

object_name(object_id) NOT LIKE '%BACKUP%' 
  or object_name(object_id) not like '%backup%'
  OR object_name(object_id) NOT LIKE '%Wi%'

In your case, object_name(object_id) not like '%backup%' will still match OR object_name(object_id) NOT LIKE '%Wi%' and so will return the row etc etc.

Change all of the above conditions to AND and you'll be OK.