Syntax problem using SELECT.. IN (@variable)

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 11, 2011 at 11:37 am

  #257150

  Hi

  Please can you advise the correct syntax when using a variable with a select statement that uses the IN operator with a variable

  some set up data:

  USE [tempdb]

  GO

  --===== Conditionally drop all the temp tables to make reruns easier in SSMS

  IF OBJECT_ID('tempdb..TBL_TST','U') IS NOT NULL DROP TABLE TBL_TST;

  CREATE TABLE [dbo].[TBL_TST](

  [MonthDate] [datetime2](7) NOT NULL

  ) ON [PRIMARY]

  GO

  INSERT INTO [dbo].[TBL_TST]([MonthDate])

  SELECT '20080101 00:00:00.000' UNION ALL

  SELECT '20080101 00:00:00.000' UNION ALL

  SELECT '20080101 00:00:00.000' UNION ALL

  SELECT '20080101 00:00:00.000' UNION ALL

  SELECT '20080301 00:00:00.000' UNION ALL

  SELECT '20090101 00:00:00.000' UNION ALL

  SELECT '20090201 00:00:00.000' UNION ALL

  SELECT '20090101 00:00:00.000' UNION ALL

  SELECT '20090201 00:00:00.000' UNION ALL

  SELECT '20090101 00:00:00.000' UNION ALL

  SELECT '20090201 00:00:00.000' UNION ALL

  SELECT '20090101 00:00:00.000' UNION ALL

  SELECT '20090201 00:00:00.000' UNION ALL

  SELECT '20090301 00:00:00.000'

  Run following code

  SELECT DISTINCT MONTHDATE

  FROM TBL_TST

  WHERE( (CONVERT(varchar( 8 ) , MONTHDATE , 3)

  IN( '01/01/09','01/02/09')))

  this works as expected and provides following results:

  2009-01-01 00:00:00.0000000

  2009-02-01 00:00:00.0000000

  So set up a sutiable variable

  DECLARE @MONTHDATE VARCHAR (MAX)

  SELECT @monthdate =

  STUFF((

  SELECT DISTINCT ',''' + CONVERT(varchar( 8 ) , MONTHDATE , 3) + ''''

  FROM TBL_TST

  WHERE

  (MONTHDATE > CONVERT(datetime , '2008-12-01 00:00:00' , 102))

  AND

  (MONTHDATE < CONVERT(datetime , '2009-03-01 00:00:00' , 102))

  FOR XML PATH( '' )) , 1 , 1 , '' );

  PRINT @MONTHDATE ----==== this appears to give correct results

  the variable returns

  '01/01/09','01/02/09'

  I want to use the output from the variable above (@monthdate) in the following statement

  DECLARE @MONTHDATE VARCHAR (MAX)

  SELECT @monthdate =

  STUFF((

  SELECT DISTINCT ',''' + CONVERT(varchar( 8 ) , MONTHDATE , 3) + ''''

  FROM TBL_TST

  WHERE

  (MONTHDATE > CONVERT(datetime , '2008-12-01 00:00:00' , 102))

  AND

  (MONTHDATE < CONVERT(datetime , '2009-03-01 00:00:00' , 102))

  FOR XML PATH( '' )) , 1 , 1 , '' );

  SELECT MONTHDATE

  FROM TBL_TST

  WHERE( (CONVERT(varchar( 8 ) , MONTHDATE , 3)

  IN ( ' + @Monthdate + ' )))

  but this doesnt return any values...???

  have tried various syntax versions and still havent cracked this...

  thoughts and comments will be appreciated

  thanks

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 11, 2011 at 11:49 am

  #1379711

  In short, you can't do that. IN takes either a resultset via a subquery or a list of literal values, not a variable.

  Options:

  * Dynamic SQL (beware SQL injection)

  * A splitter function that takes the variable and returns a table. Jeff Moden's got a good one, either at article or script here (google should find it)

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  September 12, 2011 at 4:59 am

  #1379878

  many thanks for clarification....

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• tfifield

  SSCrazy Eights

  Points: 9655

  More actions

  September 14, 2011 at 12:19 pm

  #1381539

  It's fairly easy to convert a string into something that can be used like an IN predicate. Here's an article on it.

  http://www.sqlservercentral.com/articles/T-SQL/73838/

  Todd Fifield