Does Stored Procedure useful with multiple users.

Question

0.00/5 (No votes)

See more:

Hello There

I have a C# application. While printing the report, data was fetched from multiple tables and then inserts into a new table. So the new generated table contains all the records used to print.
For this, I have created a Stored Procedure which first delete previous records and then insert new records from different tables.
Question is when a user print the report for a particular record, at the same time second user delete that record,does record will be seen by first user. If not, will SP handle this situation ?

Need Suggestion
Best Regards

Posted 10-Mar-13 21:10pm

Mayank Topiwala

Add a Solution

2 solutions

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)

**Zoltán Zörgő** · Answer 1 · 2013-03-10T21:23:00

Solution 2

It has nothing to do with using stored procedure or not.
First of all, it is a design related question: don't create tables during run (simply use a field to bind rows to the session or user), or if yo dou use temporary tables[^].
An other approach is to use a transaction: 1 - start a snapshot, 2 - select your records into the (temporary) table, 3 - do the report, 4 - rollback transaction
By the way: using transactions[^], you will probably not need that table at all - but it can affect performance, so you have to plan carefully.

Posted 10-Mar-13 21:23pm

Zoltán Zörgő

Updated 10-Mar-13 21:24pm

v2

Comments

Mayank Topiwala 11-Mar-13 5:15am

Hi Zoltan
I appreciate your response but the question is quite different.
Suppose a user select a record and going to print it, at the same time second user delete this record. So will it possible that SP handle this situation or is there any other approach I should follow ?

Zoltán Zörgő 11-Mar-13 6:57am

No, the question is not correct :). The stored procedure is just a t-sql tool, it will run in the same session, and the same transaction as any other sql statement would run. Of course, you can control transactions from within your stored procedure, but it won't help you since there is a user interaction involved too. What you need is to separate the user sessions at that point from data point of view. And that is what transactions are for, especially snapshot isolation in your case.

Mayank Topiwala 11-Mar-13 22:40pm

Snapshot isolation!
What does it mean ?

Zoltán Zörgő 12-Mar-13 2:53am

Read the second link from my answer.

Aarti Meswania · Answer 2 · 2013-03-10T21:21:00

Solution 1

two ways...
1.
you can simply add a column Userid
and use this userid to delete and add row for particular user

Or

2.
use temporary table
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/e70c61c9-5d5f-4525-8be9-3a3df3e2c0ed/[^]

Happy Coding!
:)

Posted 10-Mar-13 21:21pm

Aarti Meswania