Table type parameters

 

I was reading some artible and remembering...

One thing that my students always asked me, was "we can create a variable that is a table. How come can´t we assign a parameter to a function that is a table type?" Well, in SQL 2005 the problem is that is not possible to define inside the procedure one parameter that is a table.

Well on SQL 2008 now we can. There is an new element User-Defined Table Type. Is a data type that is actually a table definition. Look:

USE AdventureWorks2000
GO

-- Create the data type
CREATE TYPE dbo.SalesOrderItemType AS TABLE
(
    SalesOrderID    int,
    LineNumber    tinyint,
    ProductID    int,
    SpecialOfferID    int,
    CarrierTrackingNumber    nvarchar,
    OrderQty    smallint,
    UnitPrice    money,
    UnitPriceDiscount    float,
    ModifiedDate    datetime,
    rowguid    uniqueidentifier,
    LineTotal    float
)
GO

So you can create a procedure like this

CREATE PROCEDURE usp_insert_SalesOrderItem( @SalesItem dbo.SalesOrderItemType READONLY)
AS

   ... put some code here

GO

In this way, you create a procedure that receives a table variagle as a parameter, the table variable passed as argument for that must comply with the same data type, to have the same columns.

Besides, the paramter received must have, in the stored procedure definition the readonly clause meaning that the table must have the ge only read.

 

HAve fun! Remember, try to get rid of cursors, use this kind of instrument to not use cursors on your procedures or other code.