--CELKO--
3/30/2007 5:23:00 PM
>> I need to dynamically create a TABLE NAME based on a variable
@PRODUCT, but cannot seem to get it right. <<
Your whole approach is totally wrong. In RDBMS, we start with a data
model based on some real world (get a basic book on the topic) and
then create tables that reflect that reality. A table can be a set of
a particular kind of entities or a particular relationship. Notice
the word "particular" -- that means all tables are known and named.
Theya re not created on the fly -- that would be a magical world where
elephants drop out of the sky.
The rest of your is nothing a magnetic tape file system mimicked in
SQL, not an RDBMS at all. You are trying to hang a scratch tape, add
PHYSICAL row numbers and spool the "records" (huge difference between
rows and records! Learn it), so you can use the "scratch tape" in
another procedural process.
Thinking is sets, and not tapes, create a VIEW, which is a virtual
table (no such thing as a "virtual mag tape!"m is there?).
CREATE VIEW ProductCounts
AS product_id, product_cnt
SELECT product_id, COUNT(*)
FROM Products
GROUP BY product_id;
The loop and the row numbering (i.e. magnetic tape record numbers
mimicked in SQL) were useless; you can use the actual count in one
aggregation instead.
Now simply write:
SELECTproduct_id, product_cnt
FROM ProductCounts
WHERE product_id = @my_product_id;