Passing preference type parameters to a stored procedure - best practice?

Question

I need to provide a solution to the problem of passing multiple parameters to a series of reporting stored procedures in SQL Server 2008. The parameters are going to be user preferences and may consist of lists where the user has selected more than one type of value (e.g. multiple months). There may be as many as 30 different parameters and maybe 60% of them will contain multiple selections.

So far I have 3 options.

Pass in the values as normal parameters with a set interface - this seems to fail quite quickly as I would need to pass collections (multiple months would be a good example).
Pass in the parameters as a single XML fragment. This method is already used here although I'm not sure if it can cope with multiple elements of the same type (e.g. as in months above). The code uses DynaFilter to parse the XML - I've never heard of it and can't find any reference on the internet. The developer that wrote the code is back tomorrow so I'll have more info then.
Use table value parameters - I'm only just beginning to look at these but they look promising and seem to offer good performance.

We're using SQL Server 2008 with an ASP.Net MVC front end. We could move to 2012 if needed.

I've started and will continue to do more research on the best way to deal with this problem but would value any opinions on the best way forward and if there are any other options available.

Thanks in advance.

Take a look at the answer here - it applies equally well to a large quantity of parameters. I'd recommend table-valued params, I've used them and they're pretty fast. http://stackoverflow.com/questions/43249/t-sql-stored-procedure-that-accepts-multiple-id-values — SWalters, Oct 07 '13 at 17:40
Thanks Sandra. That's a good read and I'll digest it more. If I use a table valued param could I nest them ? So I had a table valued param that had column types of table valued patams ? A collection of lists ? — SteveB, Oct 07 '13 at 19:20
bhs - Unfortunately, no. One of the restrictions on [user-defined table types](http://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx) is they cannot be used as a column in another user-defined table type. You can, however, pass in two TVPs to your sproc, with a foreign key relation. I'll post an example of this as an answer. — SWalters, Oct 07 '13 at 20:16

SWalters · Accepted Answer · 2013-10-07T22:24:40.767

Here's how you can do this with table-valued parameters. These were introduced with SQL 2008, so that version would be fine for your solution, if you choose to go this route. In this test scenario I'm making a primary table and a related table with a foreign key pointing to the primary.

First, create your table datatypes:

CREATE TYPE primary_tbltype AS TABLE 
   (personkey int NOT NULL PRIMARY KEY,
    firstname varchar(30),
    lastname varchar(30))

CREATE TYPE related_tbltype AS TABLE 
   (fk_personkey int NOT NULL,
    accountnum varchar(30),
    accountbalance money)

Create your stored procedure:

CREATE PROCEDURE MySproc
    @PrimaryTable primary_tbltype READONLY,
    @RelatedTable related_tbltype READONLY
AS
BEGIN
DECLARE @CurrentKey INT
DECLARE @FirstName VARCHAR(30)
DECLARE @LastName VARCHAR(30)
DECLARE @AccountTotal MONEY

DECLARE PersonCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT personkey, firstname, lastname FROM @PrimaryTable
OPEN PersonCursor
FETCH NEXT FROM PersonCursor INTO @CurrentKey, @FirstName, @LastName
WHILE @@FETCH_STATUS= 0 BEGIN

    SELECT @AccountTotal = SUM(accountbalance) FROM @RelatedTable 
        WHERE fk_personkey = @CurrentKey

    PRINT @FirstName + ' ' + @LastName + ' - account total: ' + CONVERT(VARCHAR(30), @AccountTotal)

    FETCH NEXT FROM PersonCursor INTO @CurrentKey, @FirstName, @LastName
END
END;

And here's some test data to try it out:

DECLARE @primaryTVP primary_tbltype
DECLARE @relatedTVP related_tbltype

INSERT INTO @primaryTVP values (1, 'John', 'Cleese')
INSERT INTO @primaryTVP values (2, 'Eric', 'Idle')
INSERT INTO @primaryTVP values (3, 'Graham', 'Chapman')

INSERT INTO @relatedTVP values (1, '29310918', 28934.33)
INSERT INTO @relatedTVP values (2, '123123', 3418.11)
INSERT INTO @relatedTVP values (2, '33333', 666.66)
INSERT INTO @relatedTVP values (3, '554433', 22.22)
INSERT INTO @relatedTVP values (3, '239482', 151515.15)


EXEC MySproc @primaryTVP, @relatedTVP;

Some of the advantages of using TVPs are covered here, and they are also capable of participating in set-based operations.

Passing preference type parameters to a stored procedure - best practice?

1 Answers1