Home > SQL Server > Dynamic parameters in SQL IN

Dynamic parameters in SQL IN

2015/05/29

The SQL IN is one of the universally implemented keyword, for example

SELECT * FROM Customers WHERE STATE IN ('IL', 'AR')

This is the common syntax, but sometimes is requested to have the IN list dynamic.
In Microsoft Sql Server could be used an function, for example

CREATE FUNCTION [dbo].[GetStates](
)
    RETURNS @arrValues table(val char(2))
AS
BEGIN
   INSERT INTO @arrValues (val) VALUES ('IL')
   INSERT INTO @arrValues (val) VALUES ('AR')
   RETURN
END

Then can be used

SELECT * FROM Customers WHERE STATE IN (SELECT val FROM dbo.GetStates())

Unfortunately (still searching for an workaround…) this syntax cannot be used in grouping , for example

SUM(CASE 
   WHEN [groupcomponentcode] IN ( '1004', '1003', '1005', '1063' ) THEN Amount 
   ELSE 0 
END)  AS EngineGroup

is working , instead

SUM(CASE 
   WHEN [groupcomponentcode] IN (SELECT val FROM dbo.SomeFunc()) THEN Amount 
   ELSE 0 
END)  AS EngineGroup

gives the error
‘Cannot perform an aggregate function on an expression containing an aggregate or a subquery’

Advertisements
Categories: SQL Server
%d bloggers like this: