use Table data type in a local variable in a sql server 2008 stored procedure

I want to create a data type as a TABLE but I can't use it in a local variable in my stored procedure (SQln Server 2008)

Create type myTableDataType as table
(
    field1 int,
    field2 int,
    field3 varchar(100)
)   

create Procedure myStoredProcedureTest
as
begin
declare @temporaltable as  myTableDataType 

.
.
.
.
end

in my declare sentence I got ann error "variable has an invalid data type"

Some idea where I'm wrong!!!

Thanks

Elaina

Answers


You need a table variable:

create Procedure myStoredProcedureTest
as
begin
declare @temporaltable as table
(
    field1 int,
    field2 int,
    field3 varchar(100)
)    

.
.
.
.
end

Need Your Help

Database connection errors on EF

c# entity-framework asp.net-web-api

I am very new to entity framework and I am having a problem with a web api based site (connected to mssql) that I am writing. I keep getting seemingly random errors (mostly seeming to be database r...

Jasper Report with Map of the world and circles of different color as overlay on top of different countries

java jasper-reports report ireport

I've created a jasper report with the image and a set of small ellipse/circle on top of that image. The color of the small cirles has to change based on the data that is used for generating the rep...