SQL Server 2012 – How to Merge Two Queries into a Single One

sql serversql-server-2012t-sql

I have the two queries shown below:

declare @myTag1
select @myTag1= TagID
from tag.Tag
where TagName = 'MyTag1'

declare @myTag2
select @myTag2= TagID
from tag.Tag
where TagName = 'MyTag2'

How can I "merge" these queries into only one? I tried the below way:

SELECT CASE TagName
        WHEN 'MyTag1' THEN @MyTag1
        WHEN 'MyTag2' THEN @MyTag2
   END,
   @myValue = TagID
FROM   tag.Tag
WHERE  TagName IN ('MyTag1', 'MyTag2')

But it gives the following error.

Msg 141, Level 15, State 1, Line 9
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Any help would be much appreciated

Best Answer

To fix the syntax of your second attempt:

SELECT @MyTag1 = CASE WHEN TagName= 'MyTag1' THEN TagID ELSE @MyTag1 END
     , @MyTag2 = CASE WHEN TagName= 'MyTag2' THEN TagID ELSE @MyTag2 END
FROM   tag.Tag
WHERE  TagName IN ('MyTag1', 'MyTag2')

(I'm not sure what @myValue was doing in there as it wasn't mentioned in the first example, so I've left that out.)

You may find this inefficient (it might scan, or at least do a partial scan) due to WHERE ... IN ... so watch the query plan. If that is the case then the sub-query option would be more efficient as you'll get two seeks instead.

When using the sub-query method if there might be many rows matching, add in TOP 1 so that the query engine will stop on finding the first value:

SELECT @myTag1 = (
            SELECT TOP 1
                   TagID
            FROM   tag.Tag
            WHERE  TagName = 'MyTag1'
            )
     , @myTag2 = (
            SELECT TOP 1
                   TagID
            FROM   tag.Tag
            WHERE  TagName = 'MyTag2'
            )

(it feels logical to assume that the query planner will be clever enough to know this is what you want, but I have seen it not do so in more complex examples and the explicit TOP 1 encourage a more efficient plan)

If you need many more properties pulling out than just the one or two, then you might find a PIVOT based solution more efficient (see Scott's answer for more detail on that). Again, check the resulting query plans for confirmation (and make sure you test with realistic data sizes/patterns or you may get a different plan and therefore performance profile in Dev/Test than in Production).

A question though: is there a reason why you need this to happen in a single statement? The sub-query version should be exactly equivalent to the two statement version and some may find the time simple statements slightly clearer to read. If you are concerned about data changing between the two statements, then wrap them in an explicit transaction (or the larger process if they are part of one) to try enforce consistency.

Related Question