Webmaster Forum / ASP / Database Access / March 2007

Using MS SQL 7 - I would like to pass the name of the view to use, and
the cities (in a list)

Here's my SP

CREATE PROCEDURE sp_getcategories_CA
@classification INT,
@cities INT
AS
SELECT vr.category_description AS description, vr.category_ipk AS cid,
vr.classification_ipk AS clid,
count(product_ipk) AS products, count(vr.vendor_ipk) AS vendors
FROM view_vendor_results_CA vr WITH(NOEXPAND)
LEFT OUTER JOIN view_product vp WITH(NOEXPAND) ON vr.vendor_ipk =
vp.vendor_ipk
WHERE vr.city_id IN (@cities)
AND vr.classification_ipk = @classification
GROUP BY vr.category_description, vr.category_ipk,
vr.classification_ipk
ORDER BY vr.classification_ipk, vr.category_description
GO

Here's a typical SQL query in ASP:
    sql = "SELECT vr.category_description AS description,
vr.category_ipk AS cid, vr.classification_ipk AS clid,
count(product_ipk) AS products, count(vr.vendor_ipk) AS vendors "
    sql = sql & " FROM " & vndr_results_viewname & " vr WITH(NOEXPAND)
LEFT OUTER JOIN view_product vp WITH(NOEXPAND) "
    sql = sql & " ON vr.vendor_ipk = vp.vendor_ipk "
    sql = sql & " WHERE vr.city_id IN (" & cities & ")"'zip
    sql = sql & " AND vr.classification_ipk = " & (i+1)
    'sql = sql & thequery
    sql = sql & " GROUP BY vr.category_description, vr.category_ipk,
vr.classification_ipk"
    sql = sql & " ORDER BY vr.classification_ipk,
vr.category_description"

I found that I can't pass a list of cities eg 18,19,20 because the sp
has too many parameters, and can't figure out how to pass the view
name.

Any help?

--
Adrienne Boswell at work
Administrator nextBlock.com
http://atlas.nextblock.com/files/
Please respond to the group so others can share