Re: Problems getting a stored procedure to work
From: Joe Celko (jcelko212_at_earthlink.net)
Date: 11/08/04
- Next message: Ron: "return Time portion of datetime field?"
- Previous message: L Gonzales: "Error 3621 when casting a bit during update"
- In reply to: Ed Handley: "Problems getting a stored procedure to work"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 08 Nov 2004 10:52:00 -0800
1) Did you do that all of those SELECT/assignment statements can be
combined into one statement?
2) Did you forget that **the most basic principle** of a tiered
architecture is to have the display done in the front end and not the
back end? This is simply standard programming and software engineering;
it has nothing to do with SQL per se.
3) How are you handling a result set with more than one row in it? We
have no DDL, so we do not know about keys or anything else.
4) Didn't you find it absolutely amazing that after all the months of
careful research and design on your schema, that EVERYTHING was best
modelled as VARCHAR(n) where n in the magic numbers (50, 100, 250)? (the
exception seems to be a date stored as VARCHAR(12), so that you can
stored "Easter" as date, I suppose).
You did do months of careful research and design on your schema, didn't
you?
5) Why did you write "SELECT * FROM WHC_QuoteMem WHERE reference =
@varref;" then slowly and painfully load local variables with one row of
selected columns from that same table? SELECT * is not used in
production code, so you have a bad practice in your bad code.
6) Your names are too vague to be of much use to anyone. Date is a
reserved word, etc. Please read any book on data model and the
ISO-11179 standard.
7) Did you really mean to use FLOAT? That almost never happens in a
commercial system.
My guess is that you meant to have one query, that might look something
like this. Since you also did not post specs or DDL, this is a wild,
wild guess.
SELECT surname, K1.firstname, K1.foobar_date, K1.address, K1.addressa,
K1.addressb, K1.addressc, K1.postcode, K1.dob, K1.email, K1.cover,
K1.members, K1.type, K1.occupation, K1.insurer,
COALESCE (K1.expiry, 0) AS expiry,
DATEADD(DAY, 30, foobar_date) AS quote_exp_date,
K1.excess, K1.telephone, K1.source, K1.under, K1.quote,
M1.job, M1.foobar_id, M1.total_quotes
FROM WHC_QuoteKey AS K1
LEFT OUTER JOIN
(SELECT job, foobar_id, SUM(quote)
FROM WHC_QuoteMem
WHERE reference = @varref
GROUP BY job, foobar_id)
AS M1 (job, foobar_id, total_quotes)
ON K1.reference = @varref
AND M1.reference = @varref
WHERE .. ;
This result set would then be used in the front end to create the XML or
whatever. Mixing languages in the back end is like the label air filter
on the first Nissan (nee Datsun) truck I owned. In three inch red Roman
letters, the word "WARNING:" followed by four lines of Japanese. This
practice makes maintaining the product a screaming pain for the user :)
--CELKO--
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Ron: "return Time portion of datetime field?"
- Previous message: L Gonzales: "Error 3621 when casting a bit during update"
- In reply to: Ed Handley: "Problems getting a stored procedure to work"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
