Defining General Purpose User-Defined Table Types; Good or Bad Idea?

[DaveInIowa]

Before proceeding too far with this idea, I thought it might be good to get your thoughts here at Tek-Tips.

There are a number of stored procedures here at work where a list of values is passed in as a comma-separated-values varchar or an xml parameter. This has always seemed messy to me, so for new development, instead of following this pattern, I decided to take a new route. What I did was define some general purpose user-defined table types like so...

CREATE TYPE TableOfChar1 AS TABLE (Value CHAR(1))
CREATE TYPE TableOfInt AS TABLE (Value INT)
CREATE TYPE TableOfVarchar255 AS TABLE (Value VARCHAR(255))

More types could be created as needed, but these should suffice for most applications.

This allows me to more easily pass in multiple values to my procedures...

ALTER PROCEDURE dbo.SomeProc
    ...
    @SomeIds   TableOfInt        READONLY,
    @SomeCodes TableOfVarchar255 READONLY,
    ...

Then in my C# code I pass the values as a data table...

DataTable dtSomeIds = new DataTable();
dtSomeIds.Columns.Add("Value", typeof(int));
someIds.ForEach(i => dtSomeIds.Rows.Add(i));

var cmd = new SqlCommand("SomeProc");
cmd.Parameters.AddWithValue("@SomeIds", dtSomeIds);

I would appreciate any constructive feedback on this technique or whether or not there's a better way that I'm missing entirely.

 

[RyanEK]

You're describing table valued parameters. It's a tried technique and a good solution for your requirements.