declare [local variable] as type TABLE

[Guest_imported]

I don't arrive to declare a local variable as type table.
I tried 3 hours with the documentation of Microsoft.
No sample for that.
I'm using MS SQL7.0 und 2K.

I write stored procedures and must absolutely avoid temporary tables.

 

[fluteplr]

I am fairly sure what you want to do is not possible in 7. In 2000 you can create a user function which can return a table variable. Look at user defined functions in the BOL docs for 2000.

Just out of curiousity, what is the problem with temp. tables?

 

[tlbroadbent]

Fluteplr is correct. Table variables were introduced in SQL 2000.

Here is a simple example of T-SQL using a table variable.

Declare @tblvar table
(LastName varchar(30),
FirstName Varchar(30),
Address varchar(40))

Insert @tblvar
Select Lastname, FirstName, Address
From dbo.AddressesTbl
Where ZipCode='20202'

Update @tblvar Set Address=Upper(Address)

Select * From @tblvar Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tlbroadbent]

I have two recommendations.

1) Join Tek-Tips.
2) Ask questions about Microsoft SQL Server in forum183. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Guest_imported]

Thanks for your replies.
Meanwhile I recignized that it's available since MS SQL SERVER 2000.

Why using this? RAM is so cheap, and the performance win is so high, you can speed up stored procedeures...

Bye everybody