I want to run an update command where the user types in  a CSV value and the query runs.  If I simulate 1 number it works, but if I put in 2 variable it returns nothing (but doesn't fail)  any idea what I am doing wrong?

declare @SITE_ID int
declare @txtSchedule varchar (500)
set @SITE_ID=1
set @txtSchedule='5,6'

select * from Schedules WHERE SITE_ID=@SITE_ID and WEEK IN(@txtSchedule )

you are passng that as a string = '5,6' does not exist

try this : http://www.mssqltips.com/sqlservertip/1771/splitting-delimited-strings-using-xml-in-sql-server/

declare @table table(sno int)
insert into @table values(5),(6)

DECLARE @xml as xml,@txtSchedule as varchar(100),@delimiter as varchar(10)
SET @txtSchedule='5,6'
SET @xml = cast(('<X>'+replace(@txtSchedule,',','</X><X>')+'</X>') as xml)

select * from @table WHERE sno IN ( select N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N))

The best option is to use a table-valued parameter, but if you absolutely want a CSV, you need to unpack it into table format first. See here for (too) many methods to do that:
http://www.sommarskog.se/arrays-in-sql-2005.html