Click here to Skip to main content
15,744,193 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a database with 6 fields, 2 of which are for decimal height and width, the other 4 for Imperial height and width (feet and Inches). The users populate the desired fields. When i'm reporting/displaying on screen I have the attached code to display neatly but not sure if its efficient. Any pointers would be gladly received. Thanks!

What I have tried:

replace(case dbo.Data.DataType when 'Imperial' then
                                    case CAST(dbo.Data.Feet  AS varchar) + 'Ft ' when '0Ft ' then '' else CAST(dbo.Data.Feet  AS varchar) + 'Ft ' end 
                                    + case CAST(dbo.Data.Inches AS varchar) + 'Ins x ' when '0.00Ins x ' then ' x ' else CAST(dbo.Data.Inches AS varchar) + 'Ins x ' end +
                                    case CAST(dbo.Data.FeetH  AS varchar) + 'Ft ' when '0Ft ' then '' else CAST(dbo.Data.FeetH  AS varchar) + 'Ft '  end 
                                    + case CAST(dbo.Data.InchesH AS varchar) + 'Ins' when '0.00Ins' then '' else CAST(dbo.Data.InchesH AS varchar) + 'Ins' end
                                    LTRIM(ISNULL(CAST(ROUND(dbo.Data.SizeW, 2) AS varchar), '')) + ' x ' + LTRIM(ISNULL(CAST(ROUND(dbo.Data.SizeH, 2) AS varchar), '')) + ' ' + dbo.Data.Unit 
                                    end, '.00','') AS Size
Updated 24-Jan-19 0:47am

Maybe these answers will be of help: efficiency of the SQL query[^]
Share this answer
SQL is not known for it's efficiency when dealing with string manipulations and comparisons.
I would seriously consider moving this block of functionality out of SQL and move into the calling application.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900