my curiosity

[zathrone]

It may be a silly question but i am curious..
actually i am new to database systems but i have been reading a lot about concurrent connections that it shud be maintained while developing database applications.

I have been seeing so much stored procedures that have been using so many sql statements which create tables for temporary purposes and then drop the tables after returning the resultset.
like
- CREATE TABLE #TempUsers
- then insert some values...
- get appropriate resultset
- finally drop it

I don't know if I am wrong, but what will happen if that stored proc is accessed by more than 1 user concurrently.

My general thinking says... if 2 users accesses that stored proc.. it will try to create table with same name in the same database, which shud be impossible. wat abt 100..
( unless SQl server has special provision of treating temporary table ).

Please gimme some suggestions, i am in dilemma whether to use such stored proc or not.. I have learned to build the Stored Proc but now I am worried about consistency...;-)

[rmiao]

Local temp table is only available in current session. In sql server, temp table is created in tempdb, the real name is the name you assigned when you create it plus session info. Something like #TempUsers_nnnnn, so you'll not get dup tables when multiple users execute the sp at same time.

[zathrone]

Local temp table is only available in current session. In sql server, temp table is created in tempdb, the real name is the name you assigned when you create it plus session info. Something like #TempUsers_nnnnn, so you'll not get dup tables when multiple users execute the sp at same time.

Thanks heaps... for your reply!

As u said temp table is available in current session only, is it necessary to drop such tables explicitely ?? Will it get dropped automatically, if left undropped?

[rmiao]

It will be dropped once session closed, and you can drop it explicitely in same session.

[zathrone]

It will be dropped once session closed, and you can drop it explicitely in same session.

Thank you so much for all info..