February 25, 2014 at 6:55 am
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
February 25, 2014 at 7:03 am
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
February 25, 2014 at 7:38 am
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?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply