How to allow multi user work on same table without any issue of interact data ?

Question

1.00/5 (1 vote)

See more:

I work on sql server 2017 I have issue

How to allow mutli user working on same table without using temp table and without interact two user with same data

as example
user a :

SQL

exec sp_workingmultiuser 5

user b :

SQL

exec sp_workingmultiuser 10

SQL

create proc sp_workingmultiuser
@productid int
as
begin
    select * into dbo.testtrade from parts.tradecodes
    where productid=@productid
end

so my question if:
- user a using product id 5
- and user b using product id 10 on same time
data for user b will display for user a

so how to prevent that please ?

What I have tried:

how to allow multi user work on same procedure on same time without interact data with each others

Posted 7-Jan-22 23:33pm

ahmed_sa

Updated 8-Jan-22 3:11am

Wendelius

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2022-01-08T03:10:00

I'm not sure why you insert the data into another table. Why not simply return the data to the caller when you would have no interference between sessions.

However, if the actual situation is different and more complex, temp tables area good and clean way to separate the data between sessions. If you try to use a permanent table, you'd also need to ensure that the data is cleaned up once the session ends even in error situations. So in general I see no reason trying to avoid using temp tables, they exists for a reason.

In case you have a solid reason not to use temporary table you can add a new column to your table defining the 'context' for the data. Since you didn't explain the overall requirement it's impossible to say what this column should contain but it could be a session identifier, user identifier, system identifier etc, whatever defines the context in your case. But as said, ensure that proper cleanup is done if the data isn't permanent in nature.