Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Table Variable in SQL Server

Author  Topic 

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-18 : 04:56:37
Hi all
i am using table varaible insql server to hold the records instead of using temporary table. But am fectching more than 1000 rows for every transaction.

So is this a correct method?.. or creating temporary table is a best mehod.

and i need to use array type variables in my SP.. is table variable is a best replacement for array....


Ram

Live while u r Alive

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 05:01:31
1 Table variable can have thousands of rows
Refer these
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977
http://www.aspfaq.com/show.asp?id=2475

2 There is no array datatype
You can use the method suggested here
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

raam_kimi
Yak Posting Veteran

80 Posts

Posted - 2006-05-18 : 05:07:44
thanks madhi
i need one clarification.... if i use table variable for my process.. is it important to drop the table at the end...

Ram

Live while u r Alive
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 05:11:53
No need to drop it. It will be dropped after the scope is over. Also you cannt apply Drop table @t statement which is invalid

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-05-18 : 07:38:11
You should have a Primary key on your @TableVar or #TempTable - IME it can make a big difference to performance, particularly if you have 1,000's of rows

Kristen
Go to Top of Page
   

- Advertisement -