Check dependencies before creating function

Question

Post reply

Check dependencies before creating function

jhager

SSCrazy

Points: 2310
More actions
February 25, 2014 at 6:55 am

#148511

Hi All,

Here's the scenario: Our software is sold by module and version; therefore, a customer may or may not have a certain module, and also may have it at a wide range of versions.

The problem: We have a "Create Objects" script to deliver standardized reports and the objects (synonyms, sp's, functions, views, etc.) that the reports require. However, the reports are coded such that if they have module X, then we use that, otherwise we use module Z. This is no problem with sp's as they can be compiled without dependent objects existing; however, that's not the case with functions.

So, what I'm hoping to do is create function FX if the dependent tables/columns exist and are at the correct version or higher (module X), otherwise create a "dummy" function that just returns NULL values instead. I currently have dynamic SQL doing this, but have "hard-coded" the list of tables/columns to check for. In my view, hard-coding is failure waiting to happen.

Any ideas would be greatly appreciated; simple ideas even more so (I'd really prefer not to scan the source code and search the data dictionary for matching items, for example).

Thanks,

~ Jeff

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

yes you can, but you'll have to use dynamic SQL to hold the command for creating the functions/procedures.



IF EXISTS(SELECT 1 FROM sys.columns WHERE object_Name(object_id) = 'CriticalTable' and name = 'SpecificColumn')

   AND EXISTS(SELECT 1 FROM sys.columns WHERE object_Name(object_id) = 'CriticalTable' and name = 'OtherColumn')

   BEGIN

   EXEC ('CREATE PROCEDURE MyProcedure

        AS

        BEGIN

          SELECT 1 FRON CRITICALTABLE

        END')

   END --If

ELSE

  BEGIN

     EXEC ('CREATE PROCEDURE MyProcedure

        AS

        BEGIN

          SELECT NULL

        END')

  END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jhager SSCrazy Points: 2310 More actions · Answer 2

I've done a similar thing, using a table variable; I do

INSERT INTO @Table (@TName,@CName)

SELECT N'XYZ',N'abc'

UNION ALL

SELECT N'XYZ',N'def'

UNION ALL

SELECT N'MNO',N'ghi'

<etc.>

then check if these values exist in the data dictionary.

What I'm hoping to do is create a function only if the tables/columns the function depends on are present; however, I don't want to hard-code the table/column names. I was thinking to (somehow) use dependency, but now I'm beginning to think TRY/CATCH. If I try to create the "real" version of the function and it doesn't fail, all is well; if it does fail with error 207 or 208, I can then create the "dummy" version.

Does anyone see a problem with this method, or potential pitfalls?