In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at Table-valued Parameters (sometimes also called table types or just TVP). When you create a table type, the table definition is preserved in the database. When you create a table variable later based on that table type you will use the table definition of the table type, you don’t have to specify it again.

Table-valued parameters have the following restrictions:

_SQL Server does not maintain statistics on columns of table-valued parameters.

Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure.

Here is a small code example, this will create a new table type named SysObjectsCount with 2 columns

CREATE TYPE SysObjectsCount AS TABLE(quantity INT, xtype CHAR(2))
GO

Now when we create our variable, we can create it as type SysObjectsCount, this will be almost the same like a table variable but there is no need to specify the table structure. We can insert and select from the variable without a problem

DECLARE @mySystableCount AS SysObjectsCount

INSERT @mySystableCount
SELECT COUNT(*),xtype FROM sysobjects
GROUP BY xtype

SELECT * FROM @mySystableCount

The nice thing is that we can actually pass this table type variable into a stored procedure. Create the following stored procedure, the parameter has to be declared as READONLY. You can also not modify the table inside the proc, if you try to do so, you will get the following error

_Msg 10700, Level 16, State 1, Procedure prSysObjectsCount, Line 5

The table-valued parameter “@mySystableCount” is READONLY and cannot be modified._

CREATE PROC prSysObjectsCount (@mySystableCount SysObjectsCount READONLY, @xtype CHAR(2))
AS
SELECT * FROM @mySystableCount
WHERE xtype = @xtype
GO

Now run the following piece of code, as you can see we are running the proc three times, each time with a different value for @xtype

DECLARE @mySystableCount AS SysObjectsCount

INSERT @mySystableCount
SELECT COUNT(*),xtype FROM sysobjects
GROUP BY xtype


EXEC prSysObjectsCount @mySystableCount,'P'
EXEC prSysObjectsCount @mySystableCount,'U'
EXEC prSysObjectsCount @mySystableCount,'S'

Output


quantity    xtype
----------- -----
1365        P 

quantity    xtype
----------- -----
21          U 

quantity    xtype
----------- -----
74          S 

As you can see, the Table-valued Parameter can be passed to the proc and in the proc itself we can filter what we need.

Another example would be for reporting, let’s say the output is always the same but the input differs. You create a Table-valued Parameter, you do your logic on the Table-valued Parameter and after that you pass the Table-valued Parameter to the stored proc. This way you can reuse the proc since the logic in the proc doesn’t have to be changed.

I am sure that you written code where you populate a temporary table and then call a proc, since the proc is in scope the temporary table is available inside the proc. Table-valued Parameters could be used to replace the temporary table approach, however you can’t modify the Table-valued Parameter, so this might not work for all your needs.

That is all for today, come back tomorrow for the next post in this series, we are going to to take a look at indexing