Conditional Select & Casting in T-SQL

Ever wondered how to put a condition on a select and output the detail from a conditional cast?

Here ’stock.Variant1′ and ’stock.Variant2′ are nvarchar fields. I need to select both fields as  decimal(18,2)

ALTER PROCEDURE dbo.FileCounters_GetVariantsInInstance
(
@ProductCode varchar(50),
@Instance int
)
AS

SELECT
stock.[ProductCode],
stock.[Variant1],
stock.[Variant2],
stock.[StockLevel],
CASE IsNumeric(stock.Variant1)
WHEN 1
THEN CAST(stock.Variant1 AS Decimal(18,2))
ELSE 0.0
END AS MyOrder1,
CASE IsNumeric(stock.Variant2)
WHEN 1
THEN CAST(stock.Variant2 As Decimal(18,2))
ELSE 0.0
END AS MyOrder2

FROM
stock
WHERE
stock.ProductCode = @ProductCode
AND
stock.MetaDataInstance = @Instance
ORDER BY
MyOrder1, MyOrder2

RETURN