How do I handle multiple rows with variables defined by a query?

Question

I am trying to write a query that uses variables to feed into a sub query.

Query:

DECLARE @Code VARCHAR(5)
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

SET @Code = (SELECT Code
FROM LOOKUP.Table1
WHERE Key = @Key)

SET @Sys = (SELECT DISTINCT System
FROM LOOKUP.Table1
WHERE Key = @Key)


SELECT
@Code AS Code,
(SELECT DISTINCT 
        COUNT(ID) 
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H] 
    WHERE [Type] NOT LIKE 'X%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS Total,

(SELECT
        COUNT(Type) 
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H] 
    WHERE [Type] LIKE 'Y%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS TypeA,

(SELECT
        COUNT(Type) 
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H] 
    WHERE [Type] LIKE 'N%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS TypeB

The problem comes when [Key] is used for multiple values of [Code]:

Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Some have as many as 5 values. I can't figure out how to make this query work when SET @Code would return more than 1 value.

Answer 1

It might help to change how you approach the problem from the very beginning. There are a lot of sub-queries, all hitting the same remote table, and all with mostly the same WHERE condition. Rather than thinking about the end result as a collection of separate pieces, think about it in terms of one big set of data, and that you want to tally up how many rows meet certain criteria as you move through each row once.

Assuming that the code in the Question wasn't overly over-simplified, you should be able to do the following, which uses a Table Variable to hold multiple values of "Code" per the given "Key". Once we have those captured, then we can simply JOIN that to the remote table. We JOIN on the criteria that is common to all of the subqueries, and then place the unique criteria within the IIF().

Also, I got rid of the DISTINCT on the @Sys lookup as it was only there due to the SELECT being in a subquery which isn't necessary. And while you could use TOP 1 rather than DISTINCT, there is no reason to force a sort given that it will be the same value that comes back each time, hence the process won't notice that @Sys is potentially being overwritten up to 5 times (with the exact same value). Of course, the @Sys variable is not being used in the original query, so maybe it can be removed entirely?

DECLARE @Codes TABLE (Code VARCHAR(5) NOT NULL);
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

INSERT INTO @Codes (Code)
  SELECT Code
  FROM   LOOKUP.Table1
  WHERE  [Key] = @Key;

-- @Sys is not used in the final query. Should this be removed?
SELECT @Sys = [System] -- multiple rows may return,
FROM LOOKUP.Table1     -- but they all have the same value
WHERE [Key] = @Key;


SELECT cd.Code,
       SUM(IIF(th.[Type] NOT LIKE 'X%', 1, 0)) AS [Total],
       SUM(IIF(th.[Type] LIKE 'Y%', 1, 0)) AS [TypeA],
       SUM(IIF(th.[Type] LIKE 'N%', 1, 0)) AS [TypeB]
FROM   @Codes cd
INNER JOIN [LINKED-SERVER].[DB].[SCHEMA].[Table_H] th
        ON th.[Key] = @Key
       AND th.[Code] = cd.[Code]
GROUP BY th.[Code];

Answer 2

I would use the openquery mode. Eg:

select * from openquery(<<LINKED_SERVER_NAME>>,'your sql query goes here')

It gives you way more flexibility, given that you can make a dynamic sql query that will be ran on the server specified in the linked server's definition.

Answer 3

you could use top 1 to make sure it will output only 1 item (example below), however it is best if you add further filtering in order to bring only what you really need.

SET @Code = (SELECT top 1 Code
FROM LOOKUP.Table1
WHERE Key = @Key)