dcsimg
Results 1 to 4 of 4

Thread: Case

  1. #1
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187

    Case

    is it possible to test two fields in a case statement in SQL ?

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    select Name = case when type='U' and left(name,3) not in ('SYS','SPT')
    then 'Table -' +name else NULL end from sysobjects

  3. #3
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    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?

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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
  •