Passing info to SP

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

Re: Passing info to SP

Adrienne Boswell wrote:
> 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

You will need to use dynamic sql to have a variable data source (view).
Here is an article about using dynamic sql in T-SQL:
http://www.sommarskog.se/dynamic_sql.html

Also, T-SQL has no concept of an array. Various workarounds (and
recommendations) can be found in this article:
http://www.sommarskog.se/arrays-in-sql.html

There are several other extremely informative articles on Erland's site
that you should definitely read.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Passing info to SP

Gazing into my crystal ball I observed "Bob Barrows [MVP]" @NOyahoo.SPAMcom> writing in news:#oE#Z1fcHHA.3644@TK2MSFTNGP02.phx.gbl:

> Adrienne Boswell wrote:
>> 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
>
> You will need to use dynamic sql to have a variable data source
(view).
> Here is an article about using dynamic sql in T-SQL:
> http://www.sommarskog.se/dynamic_sql.html
>
> Also, T-SQL has no concept of an array. Various workarounds (and
> recommendations) can be found in this article:
> http://www.sommarskog.se/arrays-in-sql.html
>
> There are several other extremely informative articles on Erland's
site
> that you should definitely read.
>
> Bob Barrows

Thanks, Bob. I'll take a look when I get to work. I'll probably be
back :-)

--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share