0

Possible Duplicates:
T-SQL stored procedure that accepts multiple Id values
Parameterizing a SQL IN clause?

I have a stored procedure who have to select values with a filter system. Imagine a Table

ColA | ColB | ColC |

'A'    12     1
'B'    13     2
'C'    14     3

I need to give to stored procedure a list of ColC values for those a want the values, example (1,3) will give me 'A' and 'C'.

I know it is possible to do a IN statement like :

Select *
from Table
where ColC in (1,3)

But how make this dynamic? a solution will be to construct the sql command in a Varchar variable like 

SET @MyList ='(1,3)'
SET @SQL = '
     Select *
    from Table
    where ColC in ' + @MyList 

And then execute @SQL

But is it the best way? To make dynamic command like this is not too heavy?

Thanks for advices.

Community
  • 1
  • 1
bAN
  • 13,375
  • 16
  • 60
  • 93

1 Answers1

1

There are plenty of different options on how to pass arrays to stored procedures, however, the common practice is building the query like you have shown, hence I think it's the best solution here.

EDIT: Arrays in SQL 2008 still shows building a query as the first approach.

Ruslan
  • 9,927
  • 15
  • 55
  • 89
  • That link is way out of date. And the common practice of string buidling isn't the best way. http://stackoverflow.com/q/43249/27535 – gbn Jun 27 '11 at 09:34
  • thanks, however, it is still applicable. if you don't think so, let's see you suggest a more efficient approach. – Ruslan Jun 27 '11 at 09:43
  • The link I added matches your new link. This is the reference source. I've voted to close because of this – gbn Jun 27 '11 at 09:48
  • Thanks a lot Bad Display Name, Method 2 of your link really help me.. – bAN Jun 27 '11 at 10:19