Creating a banding Function using a CASE statement

[Zukkster]

In Access I create banding functions, that when you pass them a numerical value they return a string that can be used to band ranges of values. For example;

Public Function CouponSelection(Spend As Double) As String

Select Case Spend
Case Is > 70
CouponSelection = "Over £70"
Case 60.01 To 70
CouponSelection = "£60 to £70"
Case 50.01 To 60
CouponSelection = "£50 to £60"
Case 40.01 To 50
CouponSelection = "£40 to £50"
Case 30.01 To 40
CouponSelection = "£30 to £40"
Case 20.01 To 30
CouponSelection = "£20 to £30"
Case Is <= 20
CouponSelection = &quot;Under £20&quot;
Case Else
CouponSelection = &quot;Unknown&quot;
End Select

End Function

I want to do the same in SQL server but I'm getting a bit tied up. I'm not sure whether I can use FUNCTIONS and CASE statements in the same way.

I've done this

CREATE FUNCTION fn_Band (@WeeklySales double)
RETURNS Nvarchar(20)
as

Begin

Case
when @WeeklySales > 70.00 then 'Over £70'
when @WeeklySales between 60.01 and 75.00 then '£60 off £70'
when @WeeklySales between 50.01 and 60.00 then '£50 off £60'
when @WeeklySales between 40.01 and 50.00 then '£40 off £50'
when @WeeklySales between 30.01 and 40.00 then '£30 off £40'
when @WeeklySales between 20.01 and 30.00 then '£20 to £30'
when @WeeklySales <= 20 then 'Under £20'
else 'Unknown'

end

but I get the error

Server: Msg 170, Level 15, State 1, Procedure fn_Band, Line 1
Line 1: Incorrect syntax near ')'.

When I try to run it

 

[Zukkster]

Right OK, I've realised that I've used a VB data type (double) which get's rid of the error I was using, but the CASE statement isn't correct because the error is now

Server: Msg 156, Level 15, State 1, Procedure fn_Band, Line 7
Incorrect syntax near the keyword 'Case'.

 

[nigelrivett]

drop function fn_Band
go
CREATE FUNCTION fn_Band
(@WeeklySales decimal(18,2))
RETURNS Nvarchar(20)
as

Begin
return
Case
when @WeeklySales > 70.00 then 'Over £70'
when @WeeklySales between 60.01 and 75.00 then '£60 off £70'
when @WeeklySales between 50.01 and 60.00 then '£50 off £60'
when @WeeklySales between 40.01 and 50.00 then '£40 off £50'
when @WeeklySales between 30.01 and 40.00 then '£30 off £40'
when @WeeklySales between 20.01 and 30.00 then '£20 to £30'
when @WeeklySales <= 20 then 'Under £20'
else 'Unknown'
end

end
go


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.