May 4, 2016 at 8:58 pm
I came across this line for a variable declaration in a stored procedure, but not sure exactly how it works. Can someone help explain it:
declare @InTable dbo.BLSOrders
It seems to be some sort of temp table, but I don't understand the BLSOrders part. We have no table called "dbo.BLSOrders" (although we do have something close). I tried creating a similar method, but the error says "Cannot find data type "dbo.Invalid":
declare @InvalidTable dbo.Invalid
From here, the SP performs an INSERT INTO @InTable... query. So, I was just curious...how does this declaration work?
Thanks
May 4, 2016 at 9:32 pm
Goalie35 (5/4/2016)
I came across this line for a variable declaration in a stored procedure, but not sure exactly how it works. Can someone help explain it:declare @InTable dbo.BLSOrders
It seems to be some sort of temp table, but I don't understand the BLSOrders part. We have no table called "dbo.BLSOrders" (although we do have something close). I tried creating a similar method, but the error says "Cannot find data type "dbo.Invalid":
declare @InvalidTable dbo.Invalid
From here, the SP performs an INSERT INTO @InTable... query. So, I was just curious...how does this declaration work?
Thanks
This is a User-defined Table Type, you will find id under Programmability/Types/User-Defined Table Types.
😎
An SSMS template for creating one
-- ================================
-- Create User-defined Table Type
-- ================================
USE <database_name,sysname,AdventureWorks>
GO
-- Create the data type
CREATE TYPE <schema_name,sysname,dbo>.<type_name,sysname,TVP> AS TABLE
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply