Session State vs Dynamic Queries

[lifesupport]

I'm writing a web app (and learning at the same time) that will have daily usage by approx 100 or less users. This app will have a large variety of select statements all based on the users' selections. I want to make sure that the user's selections will not get mixed up with each other as I've read in some posts elsewhere. My solution was to write dynamic queries using the user's session ID as part of the table name. This worked but the table will not view in a grid (code below). I'm wondering if I need to be concerned about this at all. Will Session State keep everyone's selections organized when the processes go back to the server even if I use hard coded tables? What I'm concerned is that a user may get a mixture of another user's results.

What's the best route for this concern?

Code will execute but will not display. this is not good because I can't call it with Select * from @tablename

@tablename varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @SQL VarChar(1000)

select @SQL = 'select statecode, statename, statenum, nstatenum, selection '
select @SQL = @SQL + 'into ' + @tablename
select @SQL = @SQL + ' from state'

Exec ( @SQL)

 

[markros]

I don't think you need to return your results as an actual new table at all. Just simply return your results as a select statement and it should work OK for all your users. Just grab the selection criteria into parameters for your SP, different users will provide different information and it still should work OK.

Also I don't know if this would be of help or not, but in our project we used SQLWhereBuilder

http://www.codeproject.com/KB/custom-controls/SqlWhereBuilder.aspx

The previous developer of that project set it up and in my case I did just minor modifications for the page I was developing. But it may be worth to take a look.

 

[lifesupport]

I suppose I don't have to return it to a table. I was trying to do so in order to save selections made by the user. I could save the selections to an array and/or cookies. Your link looks interesting. Thanks

 

[JoeAtWork]

I want to make sure that the user's selections will not get mixed up with each other as I've read in some posts elsewhere

This really has nothing to do with how you write your SQL queries, it's how you manage your data in the web app.

Assuming you are using ASP.NET code, you could store the results in session variables. Alternatively you might store the results in some table that includes the user Session ID, and retrieve them from the table when needed again.