SQL Server Performance Forum – Threads Archive

Deferred Name Resolution

Is there a way to suppress error for non-existing object during compilation when a stored procedure run? The coding in a stored procedure/trigger will have something like:
if object exists
select * from object
else
do something else
Thanks for any help.

---

Deferred name resolution means the object does not have to exist when the stored procedure is created, but it does have to exist before it is first run. If the object in question may vary, I would suggest that it is assigned as one of the variables for the procedure. You could then, if appropriate assign a list or a coordinating IF EXISTS process. What’s it for?

---

Hi mulhall, I have an application which consists of multiple modules that are optional. I have stored procedures which are common to all modules and have references to objects of optional modules. I think you’re saying to use dynamic sql. I try to avoid dynamic sql as much as possible since it hurts performance. I remember reading some articles that Oracle will allow references to non-existing objects during run-time and it is up to developers how to handle it in coding.

---

Okay, howabout nesting the procedures : Create seperate procedures for each module (e.g. sp_module1, sp_module2, etc.) IF (query for existance of module 1)
EXEC sp_module1
ELSE
PRINT ‘module1 is not installed, procedure skipped’ IF (query for existance of module 2)
EXEC sp_module2
etc.

---

Your other option is indeed dynamic, depedning on what object or how you list your modules: EXEC sp_foreachmodule "SELECT name, ‘?..’ + name FROM ?..sysobjects WHERE type =’whatever’ "

---

]]>