INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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...

CODE --> DDL

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...

CODE --> DML

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

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

CODE --> C#

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.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close