Help with SQL Function

Hello All:
From the Following Table, I want to enter a Temperature and then have the
SQL Function Return the Web Color
-- TemperatureIndex --
ID TempMin TempMax WebColor
1 0 9 #E59DCB
2 10 19 #8569FA
3 20 29 #3F9CFB
4 30 39 #73E96F
I would like to Enter a Temperature and return the following WebColor output
15 -> #E59DCB
28 -> #3F9CFB
32 -> #73E96FCREATE TABLE TemperatureIndex
(
ID int NOT NULL,
TempMin int NOT NULL,
TempMax int NOT NULL,
WebColor char(7) NOT NULL
)
GO
INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
GO
CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
ON TemperatureIndex(TempMin, TempMax)
GO
ALTER TABLE TemperatureIndex
ADD CONSTRAINT PK_TemperatureIndex
PRIMARY KEY NONCLUSTERED (ID)
GO
CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
RETURNS char(7)
AS
BEGIN
RETURN (SELECT WebColor
FROM TemperatureIndex
WHERE @.Temp BETWEEN TempMin AND TempMax
)
END
GO
SELECT dbo.GetWebColorForTemperature(15)
SELECT dbo.GetWebColorForTemperature(28)
SELECT dbo.GetWebColorForTemperature(32)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Stuart Shay" <sshay@.yahoo.com> wrote in message
news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
> Hello All:
> From the Following Table, I want to enter a Temperature and then have the
> SQL Function Return the Web Color
> -- TemperatureIndex --
> ID TempMin TempMax WebColor
> 1 0 9 #E59DCB
> 2 10 19 #8569FA
> 3 20 29 #3F9CFB
> 4 30 39 #73E96F
> I would like to Enter a Temperature and return the following WebColor
> output
> 15 -> #E59DCB
> 28 -> #3F9CFB
> 32 -> #73E96F
>|||Dan:
Thanks for your help !!!
Best
Stuart
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uev6wbH%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> CREATE TABLE TemperatureIndex
> (
> ID int NOT NULL,
> TempMin int NOT NULL,
> TempMax int NOT NULL,
> WebColor char(7) NOT NULL
> )
> GO
> INSERT INTO TemperatureIndex VALUES(1, 0, 9, '#E59DCB')
> INSERT INTO TemperatureIndex VALUES(2, 10, 19, '#8569FA')
> INSERT INTO TemperatureIndex VALUES(3, 20, 29, '#3F9CFB')
> INSERT INTO TemperatureIndex VALUES(4, 30, 39, '#73E96F')
> GO
> CREATE UNIQUE CLUSTERED INDEX TemperatureIndex_cdx
> ON TemperatureIndex(TempMin, TempMax)
> GO
> ALTER TABLE TemperatureIndex
> ADD CONSTRAINT PK_TemperatureIndex
> PRIMARY KEY NONCLUSTERED (ID)
> GO
> CREATE FUNCTION dbo.GetWebColorForTemperature(@.Temp int)
> RETURNS char(7)
> AS
> BEGIN
> RETURN (SELECT WebColor
> FROM TemperatureIndex
> WHERE @.Temp BETWEEN TempMin AND TempMax
> )
> END
> GO
> SELECT dbo.GetWebColorForTemperature(15)
> SELECT dbo.GetWebColorForTemperature(28)
> SELECT dbo.GetWebColorForTemperature(32)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stuart Shay" <sshay@.yahoo.com> wrote in message
> news:uzg9rvG%23FHA.160@.TK2MSFTNGP12.phx.gbl...
>