Creating permanent table in stored procedure

Last Post 10 Oct 2006 03:20 PM by trans53. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
10 Oct 2006 02:31 PM
Hi guys, inside the stored procedure we need to store some data in the table and then use the information in another stored procedure.

After perm. table been created we do something like this:
insert into #temptable exec dbo.procedure_name @variable

Then inside dbo.procedure_name we use the permanent table created in the first SP to do some work.

It is a good practice to use perm. tables inside the SP and what are advantages/disadvantages of using perm. tables inside the stored procedures and if it's bad how this can be replaced?


Thanks
mwesch
New Member
New Member

--
10 Oct 2006 02:50 PM
Are you creating a permanent table or a temorary table? Your example references a temporary table.

A big issue with using a permanent table is concurrency. If you have code in your stored procedure such as:

create table PermTable (KeyField int)
insert PermTable select 1
drop table PermTable

Then you will surely have problems with concurrency as one the second user executing the procedure will not be able to create the table, will insert duplicate keys, or drop the table affecting the first user.

Using a temporary table will solve the concurrency issue as SQl Server adds a unique id per connection to the temp table name.
trans53
New Member
New Member

--
10 Oct 2006 03:20 PM
Thank you, in our case this SP will run only ones at night by only one user and i still don't like the idea creating perm. table inside the SP.


This is the sequense how it's using now in SP:

1. CREATE TABLE ACCOUNTS in dbo.getRecords
2. INSERT INTO ACCOUNTS
SELECT ....
3. WHILE (@COUNT <= @TOTAL_RECORDS)
UPDATE ACCOUNTS ....
END
4.CREATE TABLE #DETAIL_RECORDS
5.INSERT INTO DETAIL_RECORDS
(INDEX_ID,
LINE_NUMBER)
EXEC dbo.getDetailRecords 3
5. Inside dbo.getDetailRecords do something with ACCOUNTS
6. Then drop ACCOUNTS in dbo.getRecords
mwesch
New Member
New Member

--
10 Oct 2006 11:24 PM
You can make Accounts a global temp table ##Accounts.

Also, sounds like you a could potentially be opening up to a blocking issue, with updating aacounts, then updating details for each account, which then causes accounts to be updated again. Just a cauton.
trans53
New Member
New Member

--
11 Oct 2006 10:33 AM
Thank you, ##Accounts worked just fine
You are not authorized to post a reply.

Acceptable Use Policy