Using Variable Tables in SQL2

[dlewis0462]

I want to put a unique index on a variable table, but recieve this error. Could someone assist in fixing this problem?

declare @loop table (
line_id char(12),
line_hold char(12) null,
column_id tinyint,
country_id int,
service_id int,
column_n int,
column_x int null,
create unique index loopindex on @loop (line_id, column_id, country_id, service_id))


This is the error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@loop'.

 

[Bobalooey]

I believe that instead of trying to define a T-SQL variable (@loop) as a table, you want to try "Create Table" instead. Use #Loop as your table name if you want to consider it as a temporary table. The table #Loop will be automatically dropped once your SQL Connection is dropped.


create table loop
(
line_id char(12),
line_hold char(12) null,
column_id tinyint,
country_id int,
service_id int,
column_n int,
column_x int null
)
create unique index loopindex on loop (line_id, column_id, country_id, service_id)
[/b}