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