Since SQL Server 2000, you’ve been able to create variables of type TABLE, but you needed to define that table structure when (and each time) you declared the variable. You also couldn’t pass these table variables into a stored procedure.
SQL Server 2008 introduced the user-defined TABLE datatype. Now you can create and store custom-made TABLE types for use with variables and as parameters into your stored procedures. (They cannot, however, be used as the data type for a column within a table.)
TABLE types are created and dropped using the CREATE TYPE and DROP TYPE commands. There’s no ALTER TYPE command, so if you need to change a type, drop it and recreate it.
CREATE TYPE dbo.BookDetails AS TABLE ( PatronID int NOT NULL, BookID int NOT NULL, Title varchar(250), Author varchar(100)); GO
So, why would you want to pass a table into a stored procedure? Imagine you work for a library and you’re writing an app to record books that people borrow, and you use a stored procedure to insert the book details into the BooksBorrowed table. Previously, if a patron borrowed multiple books, you’d have to call that procedure once for each record, right? Maybe like this:
CREATE PROCEDURE CheckOutBooks @CustomerID int, @BookID int, @Title nvarchar, @Author nvarchar AS INSERT INTO BooksBorrowed (PatronID, BookID, Title, Author, DateBorrowed) VALUES (@PatronID, @BookID, @Title, @Author, GETDATE()); GO -- Now we have to run it multiple times to check out all the books Patron 1 wants EXEC CheckOutBooks (1, 45, 'The Grapes Of Wrath', 'John Steinbeck'); EXEC CheckOutBooks (1, 532, 'Gone With The Wind', 'Margaret Mitchell');
Not very efficient, right? Now let’s try it with the BookDetails TABLE type we created earlier.
CREATE PROCEDURE CheckOutBooks @Books BookDetails READONLY AS INSERT INTO BooksBorrowed (PatronID, BookID, Title, Author, DateBorrowed) SELECT PatronID, BookID, Title, Author, GETDATE() from @Books GO -- Now we only have to run the procedure once DECLARE @vBooks BookDetails; INSERT INTO @vBooks VALUES (1, 45, 'The Grapes Of Wrath', 'John Steinbeck'), (1, 532, 'Gone With The Wind', 'Margaret Mitchell'); EXEC CheckOutBooks @vBooks;
This reduces round-trip overhead and speeds up the entire transaction. Having a single, pre-defined definition of the TABLE type also means consistency across applications. Consistency=good. Fast transactions=gooder.
So, look it up. Play with it. Have fun, people.