CODEWITHSUNDEEP
sqlsql-server
George
George

Reputation: 1

Filter out stored procedures that was changed for the day from sys.procedures

Im trying to get a list of stored procedures that have been created for the current day or has been modified for the current day.

I have the following that works and gets me the results as follows:

SELECT  
    SCHEMA_NAME(schema_id),
    name  as StoredProcedureName, 
    create_date, 
    modify_date 
FROM 
    sys.procedures
WHERE  
    create_date >= '2018-09-12 00:00:00'  OR 
    modify_date >= '2018-09-12 00:00:00'  
ORDER BY 
    modify_date DESC

Sample data

the problem is that im trying to ignore the "SqlQueryNotification" entries. So i want it to get me the results ignoring the above mentioned name.

i tried the following

SELECT  
    SCHEMA_NAME(schema_id),
    name  as StoredProcedureName, 
    create_date, 
    modify_date 
FROM 
    sys.procedures
WHERE  
    create_date >= '2018-09-12 00:00:00' OR 
    modify_date >= '2018-09-12 00:00:00' AND 
    name NOT LIKE '%SqlQueryNotificationStoredProcedures%'
ORDER BY 
    modify_date DESC

but it still gives me the same records as the picture. its not ignoring those records, what am i doing wrong?

Upvotes: 0

Views: 58

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

  1. You need brackets around the OR criteria
  2. You don't need the placeholder in front of SqlQueryNotificationStoredProcedure
  3. You must not add a plural "s" after SqlQueryNotificationStoredProcedure

So the query could look like this:

SELECT  s.[name] AS SchemaName, p.[name] AS StoredProcedureName, p.create_date, p.modify_date 
FROM sys.procedures p
  INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
WHERE  (p.create_date >= '2018-09-12 00:00:00' OR p.modify_date >= '2018-09-12 00:00:00')
  AND p.[name] NOT LIKE 'SqlQueryNotificationStoredProcedure%'
ORDER BY p.modify_date DESC

Upvotes: 1

Ryan Wilson
Ryan Wilson

Reputation: 10765

You need to change your WHERE conditions to the following:

--Want to keep only items that were created on or after 2018-09-12 and do not have a name 
--like 'sqlquerynotification' OR items that were modified on or after 2018-09-12 and do 
--not have a name like 'sqlquerynotification
WHERE  
   (create_date >='2018-09-12 00:00:00' AND 
    name NOT LIKE '%SqlQueryNotificationStoredProcedures%') OR
   (modify_date>='2018-09-12 00:00:00' AND 
    name NOT LIKE '%SqlQueryNotificationStoredProcedures%')

Upvotes: 0

Related Questions