• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

Using a SELECT statement inside of a CASE

I have a table variable called @Results with a bunch of data in it.  One of the columns is called Info.

I'm trying to update the data in this column using the following:

                        Set Info =

                        CASE WHEN [@Results].Info = 'Other (Please explain)'
                        THEN
                        ('Other - ' + SELECT InformationTrackings.OtherInformation FROM InformationTrackings
                                WHERE  InformationTrackings.TrackingId = [@Results].Id)
                        ELSE Info
                        END

This seems to work OK if I leave off the SELECT part of the statement, and just have 'Other-'.  But when I add the SELECT part it doesn't seem to be the correct syntax.
0
delmarvamonkey
Asked:
delmarvamonkey
  • 3
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If that returns only one row make the concatenation inside the SELECT:
CASE WHEN [@Results].Info = 'Other (Please explain)'
       THEN SELECT 'Other - ' + InformationTrackings.OtherInformation 
            FROM InformationTrackings 
            WHERE  InformationTrackings.TrackingId = [@Results].Id
        ELSE Info
END

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
could you please post the entire query.
0
 
delmarvamonkeyAuthor Commented:
I'm getting an error on "SELECT" and "[@Results].Id" and "ELSE"

SELECT says "incorrect syntax near SELECT"
[@Results].Id says The multi part identifier could not be bound
ELSE says Incorrect syntax near ELSE

                        UPDATE @Results

                        SET Info =

                        CASE WHEN [@Results].Info = 'Other (Please explain)'
                                 THEN SELECT 'Other - ' + InformationTrackings.OtherInformation
                                          FROM InformationTrackings
                                          WHERE  InformationTrackings.MedicTrackingId = [@Results].Id
                                    ELSE Info
                        END
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then you don't even need the CASE statement. It's more simple than that:
UPDATE R
SET R.Info = 'Other - ' + InformationTrackings.OtherInformation 
FROM @Results R
	INNER JOIN InformationTrackings on InformationTrackings.MedicTrackingId = R.Id
WHERE R.Info = 'Other (Please explain)'

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
And like the above solution you'll only update the rows that really need to be updated. Much faster.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now