Question about using the substring() function within t-sql

[jpower69]

i am using sql serve2005 and i'm run the code below
in reporting services..

I have the following field in a table..
field is called DAY..
Day can contain the following..
record1 = 'MON,FRI'
RECORD2 = 'TUE,THU'
RECORD3 = 'MON,TUE,WED,THU,FRI'

My report has a filter labeled DAYS that coatins the following'MON,TUE,WED,THU,FRI,SAT,SUN' This give the user
an option of selecting all the days or just say mon,fri

in my stored procedure, i have the following..
if (charindex('-2',@days) = 0 ) and (charindex('-3',@days) = 0)
begin
declare @txt varchar(max)
set @txt = @days
-- Select Chosen Day(s)
set @wheredays = 'And day in ('+substring(@txt,charindex(',',@txt)+1,len(@txt)+1)+')'
end

i know that the variable contain MON when i execute the stored procedure because i hae the variable set to output.
However, when i execute the stored procedure, i get the following message..
Msg 207, Level 16, State 1, Line 19
Invalid column name 'MON'.
can anyone tell me where/what i am doing wrong here...

Thanks in advance for any comments/suggestions...

 

[gmmastros]

looks like you are missing a couple single-quotes.

set @wheredays = 'And day in ([!]''[/!]'+substring(@txt,charindex(',',@txt)+1,len(@txt)+1)+'[!]''[/!])'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Do a PRINT @wheredays . I'm sure it is going to show that you are not putting single quotes around MON, so it thinks it is a column name.

I would try to change the table design if you can. A delimited list in a single column is usually a bad idea.

 

[jpower69]

thanks...