I'm trying to use a table type variable in my stored procedure:

DECLARE @Temp3 table
(
RecordID int IDENTITY (1, 1) Primary key NOT NULL ,
FAccountID int, [Name] varchar(100), FParentAccountID int, Sublevel int,
FAccountNumber varchar(100),
FAccountType varchar(100), TotalBalance money
)

I would like to populate this table with some records and use them for some
manipulations. No problem so far. But I need to use the table variable in
loop. I would like to truncate the table and repopulate it again with a new
data.

WHILE @RowNumber <= @MaxRows
--Here is some manipulation with data from another @Temp2 variable of
table type

Then after I'm done with the current row in @Temp2 I'm trying to use
TRUNCATE TABLE @Temp3

and I get an error Incorrect syntax near '@Temp3'

I think TRUNCATE TABLE doesn't work with table type variables.
If so then how do I delete all rows from @Table3 and reset its Identity? Or
another approach - how do I deallocate @Table3 and declare and populate it
again as a fresh variable?

Thank you
vovan

On Jun 28, 9:51 am, "vovan" <some> wrote:
[..]
>
> and I get an error Incorrect syntax near '@Temp3'
>
> I think TRUNCATE TABLE doesn't work with table type variables.
> If so then how do I delete all rows from @Table3 and reset its Identity? Or
> another approach - how do I deallocate @Table3 and declare and populate it
> again as a fresh variable?
>
> Thank you
> vovan



use a #temp table instead that way you can use TRUNCATE and also DBCC
CHECKIDENT

Denis The SQL Menace
[url down]
http://sqlblog.com/blogs/denis_gobo/default.aspx

Thank you Denis.
I used to use temporary tables in my previous development. With my current
task I tried to use Cursor because I need to loop through records in the
selected set of records to make some manipulations. Looking for some samples
for using cursors I found some articles suggesting to use table type
variables instead of cursors and temporary tables because both of them are
much slower.

vovan

"SQL Menace" <denis.gobo> wrote in message
news:8550
[..]