How to use a table-valued Parameter in SQL Server 2008

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:

clip_image001[4]

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

(GameSystemName varchar(50))

Once we've run this code we should now see our new user defined table type in the SSMS object explorer as seen below.

clip_image002[4]

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)

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

            INNER JOIN @p_GameSystem gs

                  ON vg.GameSystem = gs.GameSystemName

END --proc 

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:

clip_image003[4]

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.

 

How do I get ReportItems referenced in my Report header to show on all pages?

In Reporting Services (SSRS) 2005 there are a few tricks to being able to display data items other than report parameters in the header of your report. For some reason if we return values in a dataset and want to use them in the header of the report, the values we want to use in that dataset must appear somewhere in the body of the report. Well, that’s easy enough. We can simply

  • Put a textbox in the body of the report.
  • Set its visibility to hidden
  • Drop in our dataset result
  • Reference the textbox in the header of our report like so: “=ReportItems!TextboxName.Value”.

This works great if your report is < 1 page. However if your report becomes more than 1 page, someone will eventually notice that your report header only displays those values on the first page of the report. If you need those values to appear on all pages of your report you’ll need to make a few additional tweeks to get there….namely:

  1. Add a function to our report to keep track of what our value is.
  2. Add that function call to the header of our report.

**UPDATE: The solution provided in this example only works while viewing reports online. I later noticed that while printing, or in “Print Preview” mode, items in my header were once again appearing as blank. Ultimately to work around this problem, I converted any item I needed to reference in my report header into a parameter, and set it to “internal” or “Hidden”.

To add code to our report, we’ll need to navigate to the report properties by clicking on “ReportàReport Properties…” (Remember the menu options in the report designer are context sensitive, so make sure you’re on the “Layout” tab of your report). This should open a new window, you’ll want to click on the “Code” tab and add the following function:

Dim lastDivision As String = Nothing

Function GetDivision(ByVal currentDivision As String) As String
If Not currentDivision = Nothing Then
lastDivision = currentDivision
End If
Return lastDivision
End Function

clip_image001[4]

If all has gone well, you should be looking at something similar to the image above. Click “OK” back to the designer.

Next we need to call our new function in our header to get the results. To do so all we need to type is “=Code.FunctionName(ReportItems!YourTextbox.Value)”. So in my example I’m getting a Division name that I’m looking up based on one of the report parameters, thus I named the function in this example “GetDivision”. The textbox that’s storing the value in the body of my report is named “DivisionName”……put that all together and my call in the header looks like this:

=Code.GetDivision(ReportItems!DivisionName.Value)

Now my division name will show up on all headers of all pages of my report….schweet.

You’ll probably want to change the VB we entered in the code window to more accurately reflect whatever value you’re retrieving (or more accurately, keeping track of across pages if you examine the code). Also note that if you want to do this for multiple values, you’ll need to do a bit more customization to the code, and add new variables.

Hope this helps.