How to use array of values in SQL Select - SQL Server 2005 [duplicate]

Question

Possible Duplicate:
Parameterizing an SQL IN clause?
SQL Server SP - Pass parameter for “IN” array list?

In my SP, I am taking string of data from Reference table to get the SQL select filtered.

here is scenario,

Regionids = 2,6,8 I get this value to a string @regions

In my SP i do calculations and atlast have a big SQL to return the data. Based on Calculations, i get date and ids from which to get records. Also I want to restrict rows to above region specified in Reference.

So in my Where clause of the SELECT, AND RG.regionids IN ( @regions )

I get error, Conversion failed when converting the varchar value '2,6,8' to data type int.

How can i accomplish above request?

possible duplicate of SQL Server SP - Pass parameter for "IN" array list? or Parameterizing an SQL IN clause? — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Aug 7, 2012 at 19:00

XN16 · Accepted Answer · 2012-08-07 19:13:44Z

1

The easiest way would be to use dynamic SQL:

EXECUTE('SELECT * 
         FROM   Region R 
         WHERE  R.RegionID IN (' + @regions + ')')

answered Aug 7, 2012 at 19:13

XN16

5,88916 gold badges52 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

XN16 Over a year ago

Just be aware that it is open to and SQL injection attack, especially if the @regions variable is user supplied.

Collectives™ on Stack Overflow

How to use array of values in SQL Select - SQL Server 2005 [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related