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.
Best Answer
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 useTOP 1
rather thanDISTINCT
, 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?