passing parameter to IN () within a stored procedure

• dave platt-208496

  SSC Eights!

  Points: 862

  More actions

  August 31, 2006 at 5:08 am

  #172339

  Hi, i have a stored procedure that passes an integer @para, to a SELECT statement with WHERE fieldname = @para.

  is it possible to use ... WHERE fieldname IN (@para) in my stored procedure?

  i am having difficulty with the syntax

  Thanks for your help

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  August 31, 2006 at 8:54 am

  #657836

  This works, but I am thinking you may actually want more than a single character, (or integer) passed into your @Parameter?  Hence the need for IN(...). 

  Therefore I have written with two different selects, the second being executing dynamically which allows for multiple parameters...

  CREATE TABLE #Test( FieldOne varchar(10),

                                     FieldTwo integer)

  DECLARE @Parameter1 integer

  SET @Parameter1 = 1

  INSERT INTO #Test

  SELECT 'Test 1a', 1 UNION ALL

  SELECT 'Test 1b', 1 UNION ALL

  SELECT 'Test 2a', 2 UNION ALL

  SELECT 'Test 1c', 1 UNION ALL

  SELECT 'Test 1d', 1 UNION ALL

  SELECT 'Test 3a', 3 UNION ALL

  SELECT 'Test 3b', 3

  SELECT * FROM #Test WHERE FieldTwo IN( @Parameter1)

  DECLARE @Parameter2 varchar(10),

                   @SQL varchar(100)

  SET @Parameter2 = '1, 3' 

  SET @SQL = 'SELECT * FROM #Test WHERE FieldTwo IN( ' + @Parameter2 + ')'

  EXEC( @SQL)

  I wasn't born stupid - I had to study.

• dave platt-208496

  SSC Eights!

  Points: 862

  More actions

  September 1, 2006 at 4:06 am

  #657975

  thanks very much for your help.

  you guessed right! my stored procedure works when single integer is passed to the WHERE. however i guess because using IN requires a comma to split the integers, i have to declare the parameter as varchar and i could not work out the correct syntax.

  looking at your helpful example i see i cannot use a SELECT statement but have to create the dynamic query first and then execute it.

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  September 2, 2006 at 10:50 pm

  #658194

  You don't need dynamic SQL for this...

  drop table #test

  CREATE TABLE #Test( FieldOne varchar(10),

                                     FieldTwo integer)

  DECLARE @Parameter1 integer

  SET @Parameter1 = 1

  INSERT INTO #Test

  SELECT 'Test 1a', 1 UNION ALL

  SELECT 'Test 1b', 1 UNION ALL

  SELECT 'Test 2a', 2 UNION ALL

  SELECT 'Test 1c', 1 UNION ALL

  SELECT 'Test 1d', 1 UNION ALL

  SELECT 'Test 3a', 3 UNION ALL

  SELECT 'Test 3b', 3

  DECLARE @Parameter2 varchar(10),

  SET @Parameter2 = '1,3' 

   SELECT *

     FROM #Test

    WHERE ','+@Parameter2+',' LIKE '%,'+CAST(FieldTwo AS VARCHAR(10))+',%'

  [Edit]  The dynamic SQL solutions will likely run faster, though...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• dave platt-208496

  SSC Eights!

  Points: 862

  More actions

  September 4, 2006 at 6:14 am

  #658288

  thank you - i see the logic.