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

Question

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 :

exec sp_workingmultiuser 5

user b :

exec sp_workingmultiuser 10

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 ?

Answer 1

Using SELECT INTO to create a permanent table in a stored procedure in a multi-user system is an utterly bad idea, so the simple answer is: don't do that!

If you explain in more detail what you want to achieve, we might be able to come up with some suggestions.

Answer 2

Hi,@ahmed salah
Two things that puzzle me:

1. Why not have a try on temp table (#tablename) or use variable table.
2. Is there any error message?such as timeout,lock etc.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".