day of week select with case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

day of week select with case

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-12 : 11:53:37

I am just not getting how to handle a case statement and then make it dynamic sql.
I have a date coming in then I strip the weekday value and I am trying to set
a case statement from that.

Any help please.

ALTER PROCEDURE s_cal_populate_clsavail
(
@oid int,
@ddate varchar(20)='12/12/2001'
)

AS
DECLARE @vdate smalldatetime
DECLARE @weekday int
DECLARE @strsql varchar(20)

SET @vdate=convert(smalldatetime, @ddate, 101)
SET @weekday=datepart(dw,@vdate)

SET @strsql=' AND case ' + @weekday + 'when 1 then a.sun=1 when ' + @weekday +' = 2 then a.mon=2'----....etc

select a.cloid,
b.timevalue
from tblclavaildef a
inner join tblclavailtime b on a.cloid=b.cloid
where a.oid=@oid
exec(@strsql)

not sure if I can execute the @strsql in the middle of the where clause??

Thanks

slow down to move faster...

barmalej
Starting Member

40 Posts

Posted - 2001-12-12 : 12:19:39

Try this without modifying your stored procedure and without dynamic sql.

DECLARE @weekday as int
DECLARE @vdate as datetime
SET @vdate=convert(smalldatetime, @ddate, 101)
SET @weekday=datepart(dw, @vdate)

select a.cloid,
b.timevalue
from tblclavaildef a
inner join tblclavailtime b on a.cloid=b.cloid
where a.oid=@oid
and @weekday=case @weekday when 1 then a.sun
when 2 then a.mon ---....etc
end

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-12 : 12:35:33

worked thanks.

I am always trying to make if difficult

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources