2

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

  • 2
    Do you want AND instead of multiple ORs? – wBob Jul 11 '16 at 8:57
5

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.

| improve this answer | |
  • 3
    It's Monday morning. Easy to overthink things :-) – Philᵀᴹ Jul 11 '16 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.