-
Case
is it possible to test two fields in a case statement in SQL ?
-
select Name = case when type='U' and left(name,3) not in ('SYS','SPT')
then 'Table -' +name else NULL end from sysobjects
-
Mak, this is good, and something i wasnt aware of before, however it isnt quite what i was after.
Coming from VB i would use a Case where there is a need for many If's. By this i mean that it would test the parameter against a number of different values in order to decide the next action.
pseudo code ....
If @day = "monday" then
@msg = "Hello, i hope your week goes well"
end if
if @day in ("Tuesday","wednesday","thursday","friday") then
@msg = "Hello, i hope your week is going well"
end if
if @day = "saturday" then
@msg = "I hope your week went well"
end if
would be better as ...
select case @day
case "monday"
@msg = "Hello, i hope your week goes well"
case in ("Tuesday","wednesday","thursday","friday")
@msg = "Hello, i hope your week is going well"
case "saturday"
@msg = "I hope your week went well"
end select
with this format there is only one value being tested, is it possible to test two? ie @day above and also we could add @time to change the message to include a good morning or good afternoon prefix?
i would ask the same thing in a VB forum as i dont know the answer there either, however i have come across the need whilst in SQL so i am in this forum.
should i go elsewhere?
-
declare @day varchar(10)
declare @time int
declare @msg varchar(100)
set @day='wednesday'
set @time=11
select @msg = case
when @day = "monday" and @time <12 then "Good Morning, i hope your week goes well"
when @day = "monday" and @time >=12 then "Good Afternoon, i hope your week goes well"
when @day in ("Tuesday","wednesday","thursday","friday") and @time <12 then "Good Morning, i hope your week is going well"
when @day in ("Tuesday","wednesday","thursday","friday") and @time >=12 then "Good Afternoon, i hope your week is going well"
when @day in ("saturday","Sunday") and @time <12 then "Goog Morning. I hope your week went well"
when @day in ("saturday","Sunday") and @time >12 then "Good Afternoon. I hope your week went well" end
print @msg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|