On occasion it's useful to pass an entire table of values to a stored procedure. In this walkthrough we'll take advantage of the new table-valued parameter available in SQL Server 2008. First let’s set our scenario. Let's say we have the following table:
and we want to write a stored procedure that returns all the game titles based on one or more game systems we pass in. In the past, you'd usually write a split UDF and probably pass all the values in as a delimited list in the same parameter. While that works, if you've got a huge set you're passing, your performance can take a pretty big hit as it splits apart your string. Instead we can now pass an entire table. To do so we'll first need to create a definition for our table type like so:
CREATE TYPE dbo.GameSystemType AS TABLE
Once we've run this code we should now see our new user defined table type in the SSMS object explorer as seen below.
With our table type defined lets create our stored procedure that uses our User-Defined Table Type as a parameter like this:
CREATE PROCEDURE usp_GetGames
(@p_GameSystem dbo.GameSystemType READONLY)
INNER JOIN @p_GameSystem gs
ON vg.GameSystem = gs.GameSystemName
As we can see our procedure has only one parameter named @p_GameSystem and its type is that of our User-Defined table. The rest of our procedure just selects all the game titles and game systems that are equal to whatever game systems are in our @p_GameSystem parameter. It does so by INNER JOIN’ing to our table parameter (thus only giving us results that have a match in our table parameter)
Now when we call our procedure we'll need to declare and load our parameter ahead of time like so
--declare our table
DECLARE @p_GameSystemTable AS GameSystemType
--load some values into it
INSERT INTO @p_GameSystemTable SELECT 'Nintendo'
INSERT INTO @p_GameSystemTable SELECT 'Playstation'
--call the proc and pass in our table
EXEC usp_GetGames @p_GameSystem = @p_GameSystemTable
In our case we'd get the following results from our table:
Just like that we've passed multiple values in the same parameter, and didn't have to do any string splitting or parsing....nice. This was obviously a pretty simplistic example for demonstration purposes. My hope is they make the integration of the table-valued parameters a little more robust with Reporting Services (SSRS) in the future for things like multi-select parameters. Unfortunately there's still quite a bit of scripting involved in using this with SSRS due to the fact you have to declare the table type and load it ahead of time, and there's no mechanism other than writing code to do that in SSRS at the moment. Hopefully that changes in the future.