SQL Server Performance

Deferred Name Resolution

Discussion in 'SQL Server 2005 General Developer Questions' started by pcsql, Mar 2, 2006.

  1. pcsql New Member

    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
    do something else

    Thanks for any help.
  2. mulhall New Member

    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?

  3. pcsql New Member

    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.

  4. mulhall New Member

    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
    PRINT 'module1 is not installed, procedure skipped'

    IF (query for existance of module 2)
    EXEC sp_module2
  5. mulhall New Member

    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' "

Share This Page