0

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?

Community
  • 1
  • 1
Sak
  • 87
  • 6
  • possible duplicate of [SQL Server SP - Pass parameter for "IN" array list?](http://stackoverflow.com/questions/537087/sql-server-sp-pass-parameter-for-in-array-list) or [Parameterizing an SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause) – LittleBobbyTables - Au Revoir Aug 07 '12 at 19:00

1 Answers1

1

The easiest way would be to use dynamic SQL:

EXECUTE('SELECT * 
         FROM   Region R 
         WHERE  R.RegionID IN (' + @regions + ')')
XN16
  • 5,679
  • 15
  • 48
  • 72
  • Just be aware that it is open to and SQL injection attack, especially if the @regions variable is user supplied. – XN16 Aug 07 '12 at 19:44