In clause is giving problem in dynamic sql.

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  May 3, 2013 at 12:02 am

  #150356

  Hi All,

  I am trying to run below simple query. I am trying to pass comma separated string into my query but its not working. if i pass single value then it works fine. Pklease guide.

  Declare @DayName varchar(100)

  --set @DayName = 'Monday'

  Set @DayName = '''Monday''' +','+ '''Sunday''' +','+' ''Friday'''

  select * from tbldays where weekday in(@DayName)

  select @DayName

  thanks

  Abhas.

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  May 3, 2013 at 1:03 pm

  #1612317

  abhas (5/3/2013)

  ---

  Hi All,

  I am trying to run below simple query. I am trying to pass comma separated string into my query but its not working. if i pass single value then it works fine. Pklease guide.

  Declare @DayName varchar(100)

  --set @DayName = 'Monday'

  Set @DayName = '''Monday''' +','+ '''Sunday''' +','+' ''Friday'''

  select * from tbldays where weekday in(@DayName)

  select @DayName

  thanks

  Abhas.

  SQL is a declarative language, not an interpreted language so when your code runs here is what is actually run:

  select * from tbldays where weekday in('''Monday'',''Sunday'',''Friday''')

  Which obviously will return no results.

  Is this for code that will run within a stored procedure? If so, then look into passing an XML Document to the stored procedure instead of a delimited string. Then you can shred the XML document with normal T-SQL commands and join to it from your existing query.

  If you still want to use a delimited string because XML is not your strength or because of client limitations there are string splitting functions available that will turn your delimited string into a table that you can join to in your query. Here is a good start with a T-SQL and SQLCLR option: http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Steven Willis

  SSCrazy Eights

  Points: 9893

  More actions

  May 3, 2013 at 1:25 pm

  #1612329

  Something like this is what you need. The splitter function allows you to do a set-based join to get your query results.

  --sample date table

  IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

  DROP TABLE #TempTable

  CREATE TABLE #TempTable (

  [ID] INT IDENTITY(1,1) NOT NULL,

  [WeekDay] NVARCHAR(50) NULL,

  PRIMARY KEY (ID))

  INSERT INTO #TempTable

  SELECT 'Monday' UNION ALL

  SELECT 'Tuesday' UNION ALL

  SELECT 'Wednesday' UNION ALL

  SELECT 'Thursday' UNION ALL

  SELECT 'Friday'

  DECLARE @DayName VARCHAR(100)

  SET @DayName = 'Monday,Sunday,Friday'

  SELECT

  [WeekDay]

  FROM

  #TempTable AS tt

  INNER JOIN

  dbo.DelimitedSplit8K(@DayName,',') AS dsk

  ON tt.WeekDay = dsk.Item

  Output:

  WeekDay

  Monday

  Friday