CODEWITHSUNDEEP
sqlsql-serversql-server-2008
R Loomas
R Loomas

Reputation: 333

How do I set a local variable using a CASE statement

Let me start by saying "I'm sure there's an easier way to do this but I can't find it..."

I'm trying to construct a tracking number based on 4 separate values in a record so I can concatenate the variables and insert that concatenated string into another table.

DECLARE @fy char = '';
SET @fy = (SELECT proposalCallID,
                CASE
                    WHEN proposalCallID IN (7, 8) THEN '19'
                    WHEN proposalCallID IN (5, 6) THEN '18'
                END
            FROM proposalPackage WHERE proposalCallID = 15)

I get an error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

There are 4 parts to the tracking number so I have 4 of these queries a need to concatenate the parts to make the full tracking number.

Any help is appreciated.

Upvotes: 3

Views: 16998

Answers (3)

squillman
squillman

Reputation: 13641

You're selecting 2 columns (ie- multiple values) in your query but trying to shove those into a single value variable.

Your query selects proposalCallID itself, and then another column that the CASE statement generates. The result set will therefore include two columns. SQL Server can't store two columns in a single variable. That's what is causing your error.

Try this instead:

DECLARE @fy VARCHAR(10) = '';
SELECT @fy = CASE
                    WHEN proposalCallID IN (7, 8) THEN '19'
                    WHEN proposalCallID IN (5, 6) THEN '18'
                    ELSE ''
                END
FROM proposalPackage
WHERE proposalCallID = 15;

Upvotes: 2

George Menoutis
George Menoutis

Reputation: 7260

If I understand correctly, you have one query for each one of the 4 parts of a string?

SET @fy = (query1) + (query2) + (query3) + (query 4)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271211

Is this what you are trying to do?

DECLARE @fy char(2) = '';

SELECT @fy = (CASE WHEN proposalCallID IN (7, 8) THEN '19'
                   WHEN proposalCallID IN (5, 6) THEN '18'
              END)
FROM proposalPackage
WHERE proposalCallID = 15;

Note the length specification for @fy, so the value fits. Always use length specification with character types in SQL Server.

Given the logic, you can dispense with setting the value entirely. The value returned by the CASE expression is NULL, so:

DECLARE @fy char(2);

Has exactly the same effect.

Upvotes: 4

Related Questions