Using an IN Operator as a Parameter in a Stored Procedure

Question

Using an IN Operator as a Parameter in a Stored Procedure

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

tfifield SSCrazy Eights Points: 9655 More actions · Answer 1

Sean and Gus,

Thanks so much for the example and proof of concept. I've been trying to figure out when I would actually use an input table variable that would work better than just shredding an input string into a temp table using Delimited8K.

I will be going over Sean's proof of concept with a front end developer I work with.

Todd Fifield

GSquared SSC Guru Points: 260824 More actions · Answer 2

There are advantages over a delimited list beyond just avoiding having to first concatenate the list together and then shred it back apart again.

First, the table variable has a defined data type for the column(s) you're using. That has all the usual advantages in terms of explicit data typing. In a delimited string, it's all inherently string data until/unless you convert it as part of the parsing process.

Second, you can have more than one column if you want them. Much more difficult to do in a delimited string. All kinds of options there. If you need to pass in multiple addresses, for example, you can easily split it up into the usual Address1, Address2, City, StateRegionProvince, PostalCode, with check constraints and data types, if you want them. Not at all easy to do while parsing a delimited list. You can also easily differentiate null values vs empty values in a table, which you can't do as easily in a string.

You can accomplish some of this by parsing the string into a temp table (data typing and constraints, mainly), but that again adds overhead you don't have with the table parameter version, since it's already in a table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

GSquared (3/9/2011)
There are advantages over a delimited list beyond just avoiding having to first concatenate the list together and then shred it back apart again.
First, the table variable has a defined data type for the column(s) you're using. That has all the usual advantages in terms of explicit data typing. In a delimited string, it's all inherently string data until/unless you convert it as part of the parsing process.
Second, you can have more than one column if you want them. Much more difficult to do in a delimited string. All kinds of options there. If you need to pass in multiple addresses, for example, you can easily split it up into the usual Address1, Address2, City, StateRegionProvince, PostalCode, with check constraints and data types, if you want them. Not at all easy to do while parsing a delimited list. You can also easily differentiate null values vs empty values in a table, which you can't do as easily in a string.
You can accomplish some of this by parsing the string into a temp table (data typing and constraints, mainly), but that again adds overhead you don't have with the table parameter version, since it's already in a table.

Gus,

Points well taken.

Todd Fifield