SQLTeam.com | Weblogs | Forums

T-sql 2012 passing lots of values in one parameter


#1

In t-sql 2012, I want to run a query where the value is normally an int value. I want to supply a large volume of custID values that are normally int values. I have tried to use a cast and convert values and that does not work. The query that I am trying to use is the following:

DECLARE @custID varchar(200)
set @custID = '72793,60546,91069'
select * from table
where in (@custID)

Thus can you show me the t-sql 2012 that I can use to accomplish my goal?


#2

The nice thing about SQL is that there are so many ways to do things. That is a two edged sword however and you have to take into account things like performance for what you are doing. A few values passed in would seem to be fine but it may cause problems in how you use the values in the procedure and how many you expect to have. To quote: "It Depends."

The best article I have seen on the alternatives while looking at performance is this article: Split Strings the Right Way...

That way you can decide what works best for your situation.


#3

Passing a list/array to an SQL Server stored procedure


#4

Why are you using integers for identifiers? What math do you do with them? How do you do validation and verificaiton with them? By their nature identifiers should be strings and quantities should numeric. SQL is based on sets and logic, and in particular, we do not want to use any other data structures. You might want to read any basic book on RDBMS and learn about 1NF and scalar values.

Then read these two articles: