Hey, let's break down what's going wrong here and fix it!

The error you're seeing happens because a CASE statement is designed to return a single value per row, but each of your WHEN clauses is running a subquery that can return multiple id values. SQL Server can't handle that—when you use a subquery as an expression (like inside CASE), it has to return exactly one result, not a whole set.

Correct Approach: Move Logic to the WHERE Clause #

Instead of trying to use CASE to return sets of IDs, we can build the conditional filter directly into the WHERE clause of your main SELECT statement. This way, we're filtering rows from Summit_Twitter_HashtagDetails based on your operator variable, then inserting the matching IDs into your temp table.

Here's the fixed code:

DECLARE @temp_idCol TABLE ( uid int IDENTITY(1, 1), id nvarchar(20) )
DECLARE @_ThresholdOperator nvarchar(20) = '=', @_Threshold_Val nvarchar(20) = '0.4'

INSERT INTO @temp_idCol (id)
SELECT q.id 
FROM [dbo].[Summit_Twitter_HashtagDetails] q 
WHERE q.Ticket_ID IS NULL 
AND (
    -- Match the threshold condition based on the operator variable
    (@_ThresholdOperator = '=' AND q.SentimentAnalyis_Score = @_Threshold_Val)
    OR (@_ThresholdOperator = '>' AND q.SentimentAnalyis_Score > @_Threshold_Val)
    OR (@_ThresholdOperator = '<' AND q.SentimentAnalyis_Score < @_Threshold_Val)
    -- Keep your existing IsProcessedOrNot condition
    OR q.IsProcessedOrNot = 0
)

Key Notes:

• This preserves your original logic: we're selecting rows where Ticket_ID is NULL AND the sentiment score matches the operator/value, OR rows where IsProcessedOrNot = 0 (regardless of the other conditions).
• We grouped the threshold conditions with parentheses to ensure logical precedence matches your original intent.

Alternative: Dynamic SQL (For More Flexibility) #

If you expect to support more operators in the future, dynamic SQL can make the query cleaner. Just be cautious of SQL injection risks (this is safe here since you control the @_ThresholdOperator variable):

DECLARE @temp_idCol TABLE ( uid int IDENTITY(1, 1), id nvarchar(20) )
DECLARE @_ThresholdOperator nvarchar(20) = '=', @_Threshold_Val nvarchar(20) = '0.4'
DECLARE @sql nvarchar(max)

-- Build the dynamic query string
SET @sql = N'
INSERT INTO @temp_idCol (id)
SELECT q.id 
FROM [dbo].[Summit_Twitter_HashtagDetails] q 
WHERE q.Ticket_ID IS NULL 
AND (q.SentimentAnalyis_Score ' + @_ThresholdOperator + ' @ThresholdVal OR q.IsProcessedOrNot = 0)
'

-- Execute the dynamic query with parameterized value to avoid injection
EXEC sp_executesql @sql, N'@ThresholdVal nvarchar(20)', @ThresholdVal = @_Threshold_Val

Why Your Original Code Failed #

To recap: Your CASE statement tried to return an entire result set (from the subquery) as a single value for each row in the main FROM clause. SQL Server rejects this because CASE is a scalar expression—it can only return one value per row, not multiple rows.

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