Click here to Skip to main content
13,868,400 members
Rate this:
Please Sign up or sign in to vote.
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 1:47am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Maybe these answers will be of help: efficiency of the SQL query[^]
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190214.1 | Last Updated 24 Jan 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100