Link to home
Create AccountLog in
Avatar of soozh
soozhFlag for Sweden

asked on

TSQL - Store procedure, temp table and indexes.

I am using MS SQL Server 2008.


I have a stored procedure (A) that calls a second (B).

In (B) i want to read some data from a table into a temporary table.

I want to add some indexes to the temporary table.  This is important for performance issues and this is the reason why i am working on this project

After the table has been populated i will work on it removing rows etc.

Finally i want to return the temporary table to the calling procedure (A).

Is this possible?
Avatar of ralmada
ralmada
Flag of Canada image

>>In (B) i want to read some data from a table into a temporary table.<<

Then create the temp table in the store procedure A and then you can use it in B.

create procedure A
as
begin
 create table #table1 ( ..... )
 exec B
end

create procedure B
as
begin
select * from #table1
end

Per BOL.
>>A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.<<
http://msdn.microsoft.com/en-us/library/ms174979.aspx

>> I  want to add some indexes to the temporary table<<

If you want to add indexes, check

http://www.sqlteam.com/article/optimizing-performance-indexes-on-temp-tables

My question to you would be do you really need a temp table, can you not work with a regular table instead?
Avatar of soozh

ASKER

How do i population the temp table from another table?

is it: Select into ?
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account