Click here to Skip to main content
15,664,823 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

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
Maybe these answers will be of help: efficiency of the SQL query[^]
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