SQL Server分数转小数函数报错：赋值SELECT与数据检索不可合并

Got it, let's break this down for you. That error pops up because SQL Server doesn't let you mix variable assignment and data retrieval (returning result sets) in the same SELECT statement—but yes, you absolutely can handle this with a single statement tailored to your Jamb Depth conversion need.

The Problem with Your Original (Error-Prone) Approach #

Chances are your code looked something like this, mixing variable assignment and column retrieval:

DECLARE @convertedDepth DECIMAL(10,2)
SELECT 
    @convertedDepth = dbo.ufn_ConvertJambDepthFractionToNumber([Jamb Depth]),
    [Jamb Depth]
FROM dbo.CDS_Shipments_Door_Overall_Jamb_Depth

This triggers the error because you're both storing a value in @convertedDepth and returning the [Jamb Depth] column in the same query.

Single Statement Solution #

Since your dbo.ufn_ConvertJambDepthFractionToNumber function already handles converting values like 4 1/6", 3 1/4", and 6 to decimals, you can directly call it in your SELECT list to get the converted values alongside the original data—no variables needed:

SELECT
    [Jamb Depth] AS Original_Jamb_Depth,
    dbo.ufn_ConvertJambDepthFractionToNumber([Jamb Depth]) AS Converted_Decimal_Depth
FROM dbo.CDS_Shipments_Door_Overall_Jamb_Depth

This statement will return both the original formatted value and its decimal equivalent in one go, without triggering that error.

If You Do Need a Variable (For Later Use) #

If you need to store the converted value in a variable for subsequent logic, split the operations into two separate statements instead of mixing them:

-- First, retrieve the converted value into a variable (for a single row scenario)
DECLARE @convertedDepth DECIMAL(10,2)
SELECT @convertedDepth = dbo.ufn_ConvertJambDepthFractionToNumber([Jamb Depth])
FROM dbo.CDS_Shipments_Door_Overall_Jamb_Depth
WHERE [YourRowFilterCondition] -- Add a filter if targeting one row

-- Then retrieve your data set separately
SELECT
    [Jamb Depth] AS Original_Jamb_Depth,
    @convertedDepth AS Converted_Decimal_Depth
FROM dbo.CDS_Shipments_Door_Overall_Jamb_Depth

But for your core goal of converting and retrieving the Jamb Depth values, the first single-statement approach is the cleanest solution.

内容的提问来源于stack exchange，提问作者Shelby