SQL how to check if stored procedure exists by looping through a table

Question

Let's say I have a table that has columns Name, Date, StoredProcedure in SQL Server 2016

I need to query this table and loop through the stored procedure column and check to see if this stored procedure exists in the database. If it doesn't exist, then I need to make this row null for the stored procedure column only.

This is what I tried but it doesn't work

Select m1.StoredProcedure 
from MyTable m1 
where
    IF NOT EXISTS (SELECT  *
                   FROM sys.objects
                   WHERE object_id = OBJECT_ID(N'StoredProcedure')
                     AND type IN (N'P', N'PC')) 
    Then Update MyTable m2
         Set StoredProcedure = ''
         Where m2 StoredProcedure = m1.StoredProcedure

so you just need an update statement with a join.You don't need to loop anything — S3S, Dec 17 '18 at 16:47
Possible duplicate of [SQL update query using joins](https://stackoverflow.com/questions/982919/sql-update-query-using-joins) — S3S, Dec 17 '18 at 16:47
You certainly do not need or want to loop here. That is the wrong way to attack this problem. Just use a left join between your table and sys.objects in a set based update statement. — Sean Lange, Dec 17 '18 at 16:48

score 6 · Accepted Answer · answered Dec 17 '18 at 16:48

I suspect that what you are after is:

UPDATE MT
SET StoredProcedure = NULL
FROM MyTable MT
     LEFT JOIN sys.procedures p ON MT.StoredProcedure = p.[name]
WHERE p.object_id IS NULL;

Also note that '' and NULL are not the same. In your sample SQL you have Set StoredProcedure = '', however, you state in your question "I need to make this row null for the stored procedure column". I have assumed you do want NULL and not an empty string ('').

jyao · Answer 2 · 2018-12-17T17:24:48.197

1

Though @Larnu's answer is correct, I'd like to provide two other simpler methods

update mt
set StoredProcedure = NULL
from MyTable mt
where object_id('StoredProcedure') is null;

-- or
-- at the suggestion of Larnu comment 
update MyTable
set StoredProcedure = case when object_id('StoredProcedure') is null then null else StoredProcedure end

edited Dec 17 '18 at 17:24

answered Dec 17 '18 at 17:02

jyao

1,550
3
19
26

1

Careful with that second statement. That would actually update the value of **every** row to `NULL`. A `CASE` expression that doesn't evaluate to a true condition, or an `ELSE` returns `NULL`. – Thom A Dec 17 '18 at 17:06

SQL how to check if stored procedure exists by looping through a table

2 Answers2