[egenix-users] problems with MSSQL Server functions

[Moof]

M.-A. Lemburg wrote:

>>> SELECT     *
>>> FROM         GetBestRates_Moof('20050121', '20050127', - 1, - 1, 0,
>>> 1) tab

> Makes sense: .executedirect() sends the command directly to the
> server. It doesn't prepare the command for execution first, which
> .execute() does in order to be able to cache the preparation
> step for subsequent calls with different parameters.
> 
> I still wonder why the server doesn't complain, though. I've
> never seen a database that allows using table selection based
> on bound parameters and this is actually the first time I've
> seen a stored procedure being used to determine the table
> name.

Sorry, no, I didn't explain correctly.

GetBestRates_Moof returns a set of results, which to all intents and
purposes *is* a table, and can be queried as if it were one. Think of it as
a bounded view that is generated on the fly.

I cannot have a permanent view defined for this data due to the rather
horrid database design that I have to live with for the moment. I don't need
to or want to calculate the lowest rates for every single day in the
entirety of time, which is what I'd have to do with a view, so instead I
generate it for the number of days defined in the interval (which above is
2005-01-21 to 2005-01-27). In fact, internally, it appears that the function
creates the range of dates as a resultset and promptly joins it to a bunch
of other complicated stuff.

I find working with dates the most taxing thing abotu this particular
database, especially as sometimes I'm working on real time, and sometimes
I'm working on the defined date. If you check in at 1am on the 23rd, say, it
still has to be counted as if you checked in on the 22nd for accounting and
availability purposes as you're still sleeping the night in the hotel and
need to be charged for it. The day switches over once close of business has
been declared by the receptionist, which is about 5am for most of our hotels.

Anyway, that's just me blathering on.

Does it still make sense as to why execute() isn't doing the right thing?
surely it should return an error more akin to "command cannot be prepared"
or something?

Moof
-- 
Giles Antonio Radford - <moof at metamoof.net>
Website down, but take a peek at http://del.icio.us/moof/
Homme petit d'homme petit, s'attend, n'avale / Homme petit d'homme petit, à
degrés de bègues folles / Anal deux qui noeuds ours, anal deux qui noeuds
s'y mènent / Coup d'un poux tome petit tout guetteur à gaine - Who was badly
hurt?