select 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 2005 Forums

Transact-SQL (2005)

select case

Author

Topic

dips255
Starting Member

17 Posts

Posted - 2009-07-01 : 09:18:46

hi

i have a problem when i use select case

a function that i've used is as follows

CREATE FUNCTION [dbo].[READ_UNIT]
(@VAL CHAR(1))
RETURNS CHAR(10)
AS
BEGIN
DECLARE @strunit CHAR(10)
select @strunit =
CASE @VAL
WHEN 'F' THEN 'Sq.Ft.'
WHEN 'Y' THEN 'Sq.Yards'
WHEN 'M' THEN 'Sq.Meter'
WHEN 'G' THEN 'Grounds'
WHEN 'N' THEN 'Aankadam'
WHEN 'R' THEN 'Rood'
WHEN 'T' THEN 'Chataks'
WHEN 'P' THEN 'Perch'
WHEN 'U' THEN 'Guntha'
WHEN 'E' THEN 'Ares'
WHEN 'B' THEN 'Biswa'
WHEN 'A' THEN 'Acres'
WHEN 'I' THEN 'Bigha'
WHEN 'O' THEN 'Kottah'
WHEN 'H' THEN 'Hectares'
WHEN 'L' THEN 'Marla'
WHEN 'K' THEN 'Kanal'
WHEN 'C' THEN 'Cents'
end

RETURN replace(@strunit,' ','')
END

i've got couple of other functions using select case

my problem is wherever i use these functions i get a lot of blank space with the return value, can't figure out whats the problem

Andheri (W), Apartment, Lease , 4 BHK, 1600 Sq.Ft. , Rent:Rs.91000 (31-Dec -08 )

Santacruz (E), Apartment, Lease , 1 BHK, 630 Sq.Ft. , Rent:Rs.25530 (31-Dec -08 )

Andheri (E), Apartment, Sale , 1 BHK, 1000 Sq.Ft. , Rs.50.00 Lac (19-Jan -09 )

Andheri (W), Apartment, Sale , 1 BHK, 500 Sq.Ft. , Rs.50.00 Lac (1-Jul -09 )

Bandra (W), Bungalow, Lease , 1 BHK, 900 Sq.Ft. , Rent:Rs.50000 (1-Jul -09 )

even while processing date i have the same problem

SET @dayvalue=day(@R_Date)
SET @monthvalue=replace(LEFT(datename(mm,@R_DATE),3),' ','')
SET @yearvalue=replace(right(year(@R_Date),2),' ','')

Any help would be appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 09:25:17

[code]CREATE FUNCTION dbo.READ_UNIT
(
@VAL CHAR(1)
)
RETURNS VARCHAR(10)
AS
BEGIN
RETURN CASE @Val
WHEN 'F' THEN 'Sq.Ft.'
WHEN 'Y' THEN 'Sq.Yards'
WHEN 'M' THEN 'Sq.Meter'
WHEN 'G' THEN 'Grounds'
WHEN 'N' THEN 'Aankadam'
WHEN 'R' THEN 'Rood'
WHEN 'T' THEN 'Chataks'
WHEN 'P' THEN 'Perch'
WHEN 'U' THEN 'Guntha'
WHEN 'E' THEN 'Ares'
WHEN 'B' THEN 'Biswa'
WHEN 'A' THEN 'Acres'
WHEN 'I' THEN 'Bigha'
WHEN 'O' THEN 'Kottah'
WHEN 'H' THEN 'Hectares'
WHEN 'L' THEN 'Marla'
WHEN 'K' THEN 'Kanal'
WHEN 'C' THEN 'Cents'
ELSE CAST(NULL AS VARCHAR(10))
END
END[/code]

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:40:54

why use function for this? I would have kept a table with values and their descriptions and joined to it to get description. this has the advantage that you simply add a record for every new value introduced with description rather than changing function each time.

dips255
Starting Member

17 Posts

Posted - 2009-07-02 : 05:31:34

thanks

dips255
Starting Member

17 Posts

Posted - 2009-07-02 : 05:35:19

i've used function because it returns unit of a real estate property area so its not to be updated frequently

quote:
Originally posted by visakh16

why use function for this? I would have kept a table with values and their descriptions and joined to it to get description. this has the advantage that you simply add a record for every new value introduced with description rather than changing function each time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources