使用sp_executesql计算临时表字段值的语法问题求助

The main issue with your original query is that you can't use EXEC sp_executesql directly inside a CASE expression. CASE is a scalar expression designed to return a single value, not a place to execute procedural statements like EXEC. Here's how to resolve this properly:

Solution 1: Use a Scalar-Valued Function to Evaluate Expressions #

First, create a helper function that takes a mathematical expression, executes it with sp_executesql, and returns the numeric result:

CREATE FUNCTION dbo.EvaluateMathExpression(@expression NVARCHAR(MAX))
RETURNS NUMERIC(38, 10)
AS
BEGIN
    DECLARE @computedResult NUMERIC(38, 10);
    DECLARE @dynamicSql NVARCHAR(MAX) = N'SELECT @computedResult = ' + @expression;

    -- Execute the dynamic SQL and capture the output
    EXEC sp_executesql 
        @dynamicSql,
        N'@computedResult NUMERIC(38,10) OUTPUT',
        @computedResult OUTPUT;

    RETURN @computedResult;
END;
GO

Then use this function in your query to handle both direct numeric values and calculation expressions:

SELECT 
    testId,
    CASE 
        -- Handle direct numeric values (TRY_CONVERT is more reliable than ISNUMERIC)
        WHEN TRY_CONVERT(NUMERIC(38,10), TRIM(testoutput)) IS NOT NULL 
            THEN TRY_CONVERT(NUMERIC(38,10), TRIM(testoutput))
        -- Handle expressions with "=" by extracting and evaluating the left side
        WHEN CHARINDEX('=', testoutput) > 0 
            THEN dbo.EvaluateMathExpression(RTRIM(LEFT(testoutput, CHARINDEX('=', testoutput) - 1)))
        -- Fallback for unhandled cases (adjust as needed)
        ELSE NULL
    END AS ComputedResult
FROM #testResults;

Key Notes: #

• Replacing ISNUMERIC: TRY_CONVERT is preferred over ISNUMERIC because ISNUMERIC returns 1 for some non-numeric values (like currency symbols or commas) that won't convert cleanly to a numeric type.
• Error Handling: This function assumes all expressions are valid. If you might have invalid calculations, add a TRY...CATCH block inside the function to return NULL or a specific error value instead of throwing exceptions.
• Performance: Scalar functions can have overhead with large datasets. If your temp table has many rows, consider the cursor-based approach below.

Solution 2: Cursor-Based Approach (For Larger Datasets) #

For better performance with large tables, use a cursor to process each row individually:

-- Add a column to store computed results (or use a new temp table)
ALTER TABLE #testResults ADD ComputedResult NUMERIC(38,10);

DECLARE @testId INT;
DECLARE @testOutput NVARCHAR(MAX);
DECLARE @expression NVARCHAR(MAX);
DECLARE @result NUMERIC(38,10);

-- Cursor for rows needing calculation
DECLARE resultCursor CURSOR FOR
SELECT testId, testoutput
FROM #testResults
WHERE CHARINDEX('=', testoutput) > 0 OR TRY_CONVERT(NUMERIC(38,10), TRIM(testoutput)) IS NOT NULL;

OPEN resultCursor;
FETCH NEXT FROM resultCursor INTO @testId, @testOutput;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @result = NULL;

    -- Handle direct numeric values
    IF TRY_CONVERT(NUMERIC(38,10), TRIM(@testOutput)) IS NOT NULL
        SET @result = TRY_CONVERT(NUMERIC(38,10), TRIM(@testOutput));
    -- Handle calculation expressions
    ELSE IF CHARINDEX('=', @testOutput) > 0
    BEGIN
        SET @expression = RTRIM(LEFT(@testOutput, CHARINDEX('=', @testOutput) - 1));
        EXEC sp_executesql 
            N'SELECT @result = ' + @expression,
            N'@result NUMERIC(38,10) OUTPUT',
            @result OUTPUT;
    END

    -- Update the row with the computed result
    UPDATE #testResults
    SET ComputedResult = @result
    WHERE testId = @testId;

    FETCH NEXT FROM resultCursor INTO @testId, @testOutput;
END

CLOSE resultCursor;
DEALLOCATE resultCursor;

-- View the final results
SELECT testId, ComputedResult FROM #testResults;

This approach avoids the performance hit of scalar functions by processing rows one at a time, which is more efficient for large datasets.

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