SQL Stored Procedures and Sequencing of Events...

[cerebalbore]

Hi all,

I need a bit of help understanding SQL server 2005 and custom stored procedures. I’m by no means an expert at using SQL, I can do quite a bit of querying and manipulation, but this has me stumped because the terms I use to describe the potential issue aren’t right (or they mean something different). I want to use a stored procedure to replace a query because so should be much faster than the common table expression I’m using at the moment (I have a Cartesian join - on purpose), and less aggro to update, but I would need to use temporary tables (#temp) to gain a massive time advantage.

I have 3 basic steps to my new query:
1 – if it exists, drop #temp1 and #temp2
2 – create #temp1 and #temp2
3 – query temp tables

This would be called by multiple instances of my application, so what would happen if person A starts the query, followed by person B like in this time flow:
1 – person A – if it exists, drop #temp1 and #temp2
2 – person A – create #temp1 and #temp2 (parameter A)
3 – person B – if it exists, drop #temp1 and #temp2
4 – person B – create #temp1 and #temp2 (parameter B)
5 – person A – query temp tables
6 – person B – query temp tables

At step three above, person A's temp tables are destroyed and person B's tables are created instead, so when step 5 happens they might get the results from person B's query...

My question is this “Does a stored procedure complete before it is called again, or can it be called in parallel”?

If it can be called in parallel, does anything stop the above situation happening (so I don't need to worry), or do I need to do something else?? I googled about and found that connections come into play somewhere along the line, and, if my understanding is correct, person A's connection can't see person B's temp tables and vice versa.

How can I test this scenario to see for myself - can't step through the stored procedure in SSMS2005...

Cheers,

Kat


I'm such a noob

 

[starsky51]

Hi Kat.

Temporary tables are unique to each session. If two people create temp tables with the same name in two different sessions, they would each be using separate tables.

It is possible to create a global temp table by prefixing the table name with ##. A global temp table would work the way you describe.

Dave.

 

[Qik3Coder]

Kat,
Sidebar/Clarification on what starsky51 said.

Table variables are perfect for lighter datasets, usually under a thousand rows. These are connection specific and work like any other variable created.

You do not need to explicitly drop these when done.

Lodlaiden

You've got questions and source code. We want both!

 

[cerebalbore]

Hi guys,

Thanks, knew someone here would know!

Kat


I'm such a noob

 

[gmmastros]

How can I test this scenario to see for myself

Each query window in SQL Server Management Studio is a separate connection to the database.

So... open 2 windows.

In Window 1:

Create table #Temp(Data VarChar(20))
Insert Into #Temp(Data) Values('This is connection 1')

In window 2:

Select * From #Temp

You will get an error because the #Temp table does not exist in this session.

Now, in window 2:

Create table #Temp(Data VarChar(20))
Insert Into #Temp(Data) Values('This is connection 2')

Select * From #Temp

As you can see, there is only 1 row (with connection 2).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom