SQL2000 : Changing a hard-coded Select statement

[~J~]

Weird to explain, but the reasons are critical.

Got a customer who have purchased a 3rd party piece of software that looks at a SQL server and retrieves values from a SQL statement.

The problem is however is that the SQL statement is wrong, and for reasons which I can't explain, the SQL statement will NEVER get changed as it's hardcoded into the program.

What I want to do however, is create some kind of trigger that runs a rule and if one condition is met, returns one value or another.

Let me explain a bit better.

Suppose the hard-coded SQL statement is this:

SELECT Price,VATCode FROM Products.

Simple enough, easy to understand.

However what I want to do is add 'something' on the SQL side of things that examines certain conditions BEFORE it returns Price and VATCode, and if a condition is met, changes the value of VATCode to something else.

I don't think I can create a "Select-Trigger" (only insert, update or delete), but was wondering if anyone knew of an alternate way of doing this.

Would really appreciate it if anyone had any thoughts.

 

[Mr^B]

1. Rename the Products table to something else.

2. Create a view called "Products" which returns the data, but uses a SQL function to calculate the VATCode.

 

[~J~]

Wish it were that simple.

That 'actual' code that is 'wrong' is:

SELECT l.LabelType, l.PalletLabelID, l.DelSchedID, l.AsnID, d.BasicPartNo, d.LatestPartNo, d.Description, d.PartColour, d.ContainerCode, d.PackFactor FROM Label l INNER JOIN DeliverySchedule d ON l.DelSchedID = d.DelSchedID WHERE l.SupplierID = 1 AND l.WarehouseID = 1 AND l.LabelID = ####

Cheers anyway.

 

[Mr^B]

So?

You can still have views replacing the two tables in that select statement. It's just a bit more complicated than the simplistic example - and why we get paid to do it.

 

[pinkaardvark]

I guess you either need to present the data in a new way to the existing select ie via views, do some trigger hack(ie I beleive you can do something with profiler to hack up a select trigger) or modify the actual SQL server driver your using to add a hook for that Select request.