SQL Server - tables specific to each user

Question

Here is my scenario:

The user enters a date range. A stored procedure is being executed that populates other tables based on dates. The user can then click a button to view these tables (another SP that selects from the tables).

Question:

There are several users. Each user should be able to have their own tables, so to speak, because date ranges can be different. Right now, user 1 starts SP, which fills the table with dates A and B. Then user 2 starts SP, which fills the table for dates C and D. Now user 1 goes and clicks the browse button, waiting for data for dates A and B, but what happens data for C and D is displayed because the last user to run SP used the dates C and D.

How can I store data separately / locally for each user?

Thanks.

Answer 1

If you really need to save the results of the query, you need to assign a result set to either the user or generate a script identifier each time the user runs the report.

The easiest approach is to simply run the query as a report and not save the results. Recalculate the output and simply unload it every time the user runs the report (i.e. do not save the results).

If you need to save the results, and each user must work with one date range at any time, then the table can be annotated using the user ID.

If a user can work with several scripts, then for each user there should be a different checklist with scripts. When the user runs the run, then a new script identifier is generated. You will need a screen that allows users to select a scenario in this case, and possibly some means to clean outdated ones.

Answer 2

You can add another column to the table, and then, when you fill it with data, add a variable / session identifier to this column.

Then the query, which is then selected from this table, must be updated to select where the session ID / user ID matches.

Answer 3

It seems that you delete the whole table every time, instead, you try to delete only the data for the user executing the query. Use the WHERE clause in SELECT so that each user sees their own data. This would mean that you need to save the username / identifier with each row of data if you have not already done so.

Answer 4

Consider storing the user ID with each value added, and then enter the user ID in all of your queries. Thus, you can use 1 table and 1 stored procedure.

So, if user A starts the stored procedure, he will add entries A-1A, A-2A. When user B starts the stored procedure, he adds entries B-1B, B-2B, B-3B, etc.

When you click the browse button, just call the second stored procedure, in which you again pass the user ID, and it returns the rows for that user ID.