Conditional database field

[Glasgow]

I have a database whose schema will vary slightly for each client who buys our application. So, some tables and some fields in a table may only exist only for specific clients. However, this appears to cause problems because, when running certain scripts (from SQL Management Studio Express), it spits out an error. Fir example, the following code:

IF (SELECT VATapplies FROM Settings) = 'Yes'
  BEGIN 
  UPDATE Client SET VatNo='1234 564 789'
END

will fail because the field VatNo does not exist in the Client table (because VAT/tax does not apply for this client and the field does not exist).

Is there a way around this other than by making such fields unconditional (i.e. include them for all clients and leave them null for those clients to whom they are irrelevant)?

 

[Glasgow]

I guess changing the code to:

IF (SELECT VATapplies FROM Settings) = 'Yes'
  BEGIN 
  EXEC ('UPDATE Client SET VatNo=''1234 564 789''')
END

would probably work but I guess I'm just wondering whether I'm doing something very dumb or missing something more obvious!

 

[MDXer]

I've worked on Hotel, Casino and food and beverage systems that have optional fields. The way these have been handled is a generic name was given to the column such as CustomField1 and then the data dictionary notes what these fields can be used for.

 

[Glasgow]

Thanks. I have used that very approach in some cases also - but only really to support "user defined" fields.

It's certainly an option I guess.

 

[JoeAtWork]

Please don't tell me you have multiple Client tables?!

The way to do this in a relational database is either:
1. Have all possible fields in the table, populate them according to the business rules of your app
2. Create some sort of Optional or Custom fields table similar to MDxer suggestion

But having multiple tables that basically store the same information is just a really bad idea for maintaining a database.

Regarding your original error, does this not point to a logic or data-entry error? It could only have gotten to the UPDATE statement if VATApplies = "YES", so either that does not mean "customer uses the VatNo field", or the customer's record is incorrectly set up.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[gmmastros]

I have another idea. It'll likely mean more work for you, but it should work well, too.

I suggest you split the stored procedure in to 2 stored procedures. One of the stored procedures would exist in every database (for all clients), and the other stored procedure would only exist for those clients that need it.

First, 'attempt' to create a stored procedure to update the VatNo.

Create Procedure _UpdateVatNo
  @VatNo VarChar(20)
As
UPDATE Client SET VatNo=@VatNo

If the VatNo column does not exist, the above stored procedure will not be created. But that's ok. It's all part of the plan.

Next, make a stored procedure that exists for all clients. This is the stored procedure that would be called from your front end application.

Create Procedure UpdateVatNo
  @VatNo VarChar(20)
As
If Exists(Select * From Information_Schema.Routines Where Specific_Name = '_UpdateVatNo')
  Begin
    Exec _UpdateVatNo @VatNo
  End

When you create this second stored procedure, it will not error. However, if the _UpdateVatNo stored procedure does not exist, you will get a message like this...

[tt][blue]Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table '_UpdateVatNo'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.[/blue][/tt]

The message claims that it cannot be successfully executed, but it lies. The reason it works is because we check the database to see if it has a procedure named _UpdateVatNo before we try to execute it.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[EKOnerhime]

Could you check directly in INFORMATION_SCHEMA.Columns view?

e.g.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Client' AND COLUMN_NAME = 'VATNo')
BEGIN
  UPDATE Client SET VatNo='1234 564 789'
END

 

[Glasgow]

Thanks Joe (have yet to look at posts following his but thanks)

The code shown is by way of a simplified example only.

There is only one such table in the database but the fields in that table can theoretically vary according to who is buying the application. I won't pretend that it's an ideal approach but what is more interesting to me now is the point you raise:

It could only have gotten to the UPDATE statement if VATApplies = "YES"

I'm not convinced this is the case - i.e. it appears to be failing when VATApplies = 'No' and this is what threw me initially and sparked the post. If I change the condition to IF 1=0, it still fails.

 

[gmmastros]

That's happening because SQL parses the entire query before running it. Since the column doesn't exist, the parsing part fails and the query is not run. Parsing will succeed if you use another stored procedure like I explained in my previous post.

Makes sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

George:Thanks for your solution. I can see how it or a variation of it might work.
EKOnerHine: While your suggestion might be more bulletproof, I think that would leave me with the same problem (e.g. as per the IF 1=0 scenario I describe above) - i.e. it will still 'examine' and complain about the field even though the condition under which it will execute has not been met.

 

[Glasgow]

George Yes makes sense and what I suspected although it frustrates me a little! I have found that similar parsing issues will cause a script to fail on first attempt but succeed on subsequent attempts without any editing.

 

[EKOnerhime]

Glasgow: It doesn't do a pre-check on SQL 2005 SP2. I'm using a 64-bit flavour and have tested it using the script direct and an SP. How very odd! If you find out why, please post, I'm quite curious.

 

[Glasgow]

Interesing. I am using SQL 2005 Express SP2 and executing as a script within Management Studio Express.