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

Question

Post reply

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

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
January 8, 2022 at 9:40 am

#3971146

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 ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

ahmed_elbarbary.2010 wrote:

data for user b will display for user a

If that's happening, then you've left something out of your post because, according to the code you've posted, it cannot happen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

What is the problem your trying to solve?

If you want data to be different in the table you need to either wait for 1 person to finish then delete the data for the next to come in and use the table fresh or use another filter on the temptrade table further down the code block, or use temp tables.

I guess the bones of that stored proc are much more meatier than you have provided so potentially a little more context may produce a better answer to your problem.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

With that, I'll ask, are you really saying that you want to restrict the two users from seeing each other's products at any time and not just for the code example you posted? In other words, "Row Level" security?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 4

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

January 9, 2022 at 7:53 am

#3971287

yes yes yes

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Another good link can be found here.

https://www.sqlshack.com/introduction-to-row-level-security-in-sql-server/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)