This might help save your state concerns, but the implementation takes a bit
of work. If you want to store a list of product_ids for for a users
'session' , One approach is to have a table such as USER_PRODUCT_IDS, with
fields ID int [identity], USERID int, PRODUCT_ID int, cluster on USERID AND
PRODUCT_ID. When you need to store user settings, you insert the records
into the USER_PRODUCT_IDS table. Then when you run each of your stored
procedures, just add on 'WHERE EXISTS SELECT PRODUCT_ID FROM USER_PRODUCT_ID
WHERE USERID = @USERID AND PRODUCT_ID = @PRODUCT_ID' .

Whenever a new session is started in your app, you prefix it by doing a
DELETE FROM USER_PRODUCT_IDS WHERE USERID = @USERID. So this way you don't
have to worry how long the users session lasts. Every time you start a new
session for that user, you purge the existing records and repopulate.

Hope this helps.

"Michael Shutt" <mshutt@advectis.com> wrote in message
news:3d63b6c6$1@10.1.10.29...
> Thanks for the info. I have oversimplified the description of my
particular
> problem. I guess I was hoping for magic bullet but it doesn't sound like
> there is one.
>
> I'm leery of the scalability of the temporary table approach since a
"cached
> list" of PRODUCT_ID values may need to be available for each user and for
> extended periods of time (i.e., 1 hour). Also, I may not know exactly how
> long the list needs to be available, so I won't necessarily know when I
can
> drop a temp table to freeup the resources. This is probably worth some
more
> research on my part though.
>
> Since the query to get the list of PRODUCT_IDs can be quite expensive,
> option 2 is what I am trying to avoid. I can essentially get the list one
> time for each user session, but it needs to refresh each time user begins
a
> session.
>
> A variation of option 3 is what I am currently using (generation of
dynamic
> SQL statements), but I loose out on the ability of SQL Server to
precompile
> and optimize the queries.
>
> Damned if you do, damned if you don't...
>
> "Q*bert" <luke_Davis_76@hotmail.com> wrote in message
> news:3d63a166$1@10.1.10.29...
> >
> > Not sure why you have to get the array results from one and pass to
> another.
> > You could do one of the following
> >
> > 1) Exeucte a sp that creates a temp table where the resutls are stored
> > using this table, you would have your array for the other sp's.
> >
> > 2) Execute the same query in each sp
> > something like
> > SELECT * FROM SALES
> > WHERE PRODUCT_ID IN (SELECT PRODUCT_ID FROM PRODUCTS WHERE COLOR =
'red')
> > The only value you'd be passin in is the 'red' which you already have.
> >
> > 3) Use a cursor for the results of the initial query, and concat a sting
> > consisiting of the results.
> >
> > Any one of these has benefits over the other, all have thier own
> shortfalls.
> >
> > Hope this helped
> >
> > Q*bert
> > *!@(^
> >
> >
> >
> > "Michael Shutt" <mshutt@advectis.com> wrote:
> > >I am developing a database (on SQL Server 7) to be access via ADO and
> have
> > a
> > >small dilemna. Ideally, I could to execute all queries using stored
> > >procedures, but I can't figure out how to do the following.
> > >
> > >Say I've got two tables. The first table is named 'PRODUCTS' as has a
PK
> > >column named 'PRODUCT_ID'. The second table is named 'SALES', which
> > >contains a 'PRODUCT_ID' column (which is a FK of PRODUCTS.PRODUCT_ID).
> > I
> > >need to execute a series of database queries against the SALES table,
all
> > of
> > >which will have the same WHERE clause using a list of PRODUCT_IDs.
> > >
> > >For example, to get the list of product ids I may first use this query
> > >
> > >SELECT PRODUCT_ID FROM PRODUCTS WHERE COLOR = 'red'
> > >
> > >From the results of that query, I would have a list of PRODUCT_IDs
(i.e.,
> > 1,
> > >2 & 3) which would be used to execute the next queries
> > >
> > >SELECT * FROM SALES
> > >WHERE PRODUCT_ID IN (1,2,3)
> > >
> > >SELECT * FROM SALES
> > >WHERE PRODUCT_ID IN (1,2,3) AND ORDER_DATE = '1/1/2000'
> > >
> > >SELECT * FROM SALES
> > >WHERE PRODUCT_ID IN (1,2,3) AND SALESMAN = 'Tom'
> > >
> > >What I would like to do is have 4 stored procedures to execute these
> > >queries, something like:
> > >
> > >spGetProductsForColor @Color varchar(10)
> > >spGetAllSalesForProducts @ProductIDs ???
> > >spGetSalesForProductsAndDate @ProductIDs ???, @Date datetime
> > >spGetSalesForProductsAndSalesman @ProductIDs ???, @Salesman varchar(20)
> > >
> > >But I don't know how to pass a list of delimited values to be used for
an
> > IN
> > >clause in the stored procedure. Or maybe there is a whole different
> > >approach to solving this problem? I would like to avoid running a
query
> > >against the PRODUCTS table each time I execute the searches against the
> > >SALES table, so nesting queries is not the solution I am looking for.
> Any
> > >ideas would be appreciated.
> > >
> > >MJS
> > >
> > >
> > >
> >
>
>