SQL Query Where X IN swappable variable depending on search?

Question

I'm a bit new to SQL queries and I want to make my life easier by using one query to search a complex table, but change out a variable in the "WHERE x IN @variable1" sort of deal. The background on this is that I have a massive database of computer data that is inventoried daily and updated with details relevant to my job. Every week I need to perform a query for several different results from the same database pulling the same tables, but I need to use different criteria.

For example, my SQL query is HUGE (pulling over 30 columns, performing joins, etc. It works perfect). However at the end I have a line that says something like:

WHERE COMPUTER.Features IN ('Online, 'Active', 'Recent')

But then I have to perform the same query, but swap out the "IN ..." part like this:

WHERE COMPUTER.Features IN ('Decommission', 'Offline', 'Refresh', 'Ticketed', 'In-Transit')

I have MANY different use cases (which are commented out in the same file) and every day I have to copy/paste the new set of "IN ..." conditions.

What I'm thinking would work awesome is if I could just use variables to make my life easier. So I could just do something like:

WHERE COMPUTER.Features IN @Active

or

WHERE COMPUTER.Features IN @Problems

or 

WHERE COMPUTER.Features IN @UserHelp

and so on. I have declared one variable as a test like this:

DECLARE @Active VARCHAR
SET @Active = '(''Online'', ''Active'', ''Recent'')'

That way instead of having to copy/paste all the values every time, I can just change @UserHelp to @Active and hit Go.

Is this possible? I've seen some other examples online and it seems overly complex for what I'm trying to do. Any help would be appreciated.

`IN (@Variable)` would be the same as ` = @Variable`. T-SQL doesn't take a string that contains a delimited set of values and split it into separate values. You'll need to use a string splitter or a table type parameter here. — Thom A, Mar 04 '22 at 16:00
Does this answer your question? [Passing a varchar full of comma delimited values to a SQL Server IN function](https://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — Thom A, Mar 04 '22 at 16:01
The (correct) way to do this is to write a stored procedure that accepts a user-defined table as a parameter and then use that in your IN clause (i.e., `.. IN @tableParameter `). — RBarryYoung, Mar 04 '22 at 16:03
Note that my answer to @Larnu's link is the accepted answer, but it is only really appropriate for ad-hoc queries. ***You should not use it in a production application*** unless you understand how to make it secure. Please use one of the other answers or my suggestion above. — RBarryYoung, Mar 04 '22 at 16:05
@RBarryYoung `IN @TableParameter` would generate an error. YOu would need to `JOIN` to the table type parameter or use a subquery. — Thom A, Mar 04 '22 at 16:06
@Larnu Ack! You are right, of course. I meant `.. IN (Select column From @tableParameter)`. — RBarryYoung, Mar 04 '22 at 16:07
Not going to lie, I'm not a fan of any of those answers now I look at them properly... Maybe I should post an answer there or one of the dupe candidates that actually demonstrates the "good" solutions (though there most be a good canonical dupe already that has that... Anyone better at "Google-Fu" than I am?). — Thom A, Mar 04 '22 at 16:12
Where do these lists of features come from - are the different collections of features static or created on the fly? — Stu, Mar 04 '22 at 16:20
Larnu, your comments make sense in that SQL doesn't want to do the splitting for us. The link you mentioned does seem like it would be applicable in some regards. I'm not sure how I could quickly swap out the list provided without confusing the beans out of my coworkers (who also use the same saved query file to do the job too). — Mr Hobbits, Mar 04 '22 at 16:23
Does this help - [How do I pass a list as a parameter in a stored procedure?](https://stackoverflow.com/a/42451702/1048425) - it references a stored procedure, but the method is applicable to any kind of parameterised query. — GarethD, Mar 04 '22 at 16:24
Stu, the set of features comes from the database. As customers/clients update their systems or provide trouble tickets the status of over 300,000 machines changes. Other software also performs checks on the 'health' of the systems and updates the 'features' daily. — Mr Hobbits, Mar 04 '22 at 16:25
`DECLARE @Active VARCHAR` You have created a variable-length, 1 character string. That will not work regardless of any suggestion / answer. — SMor, Mar 04 '22 at 17:06
@SMor makes sense, what's your suggestion for a 'correct' way to do it? — Mr Hobbits, Mar 04 '22 at 17:12

score 0 · Accepted Answer · answered Mar 04 '22 at 22:36

One possibility is to use a table variable (or temp table up to you) to store the features you want to query against. And then have a single variable which you set (or pass as a parameter), which is used to conditionally populate the table table variable e.g.

DECLARE @FeatureSection varchar(32) = 'Active'; -- 'Problems' 'UserHelp'

DECLARE @FeatureToReport TABLE ([Name] varchar(32));

IF @FeatureSection = 'Active' BEGIN
    INSERT INTO @FeatureToReport ([Name])
        VALUES
        ('Online'), ('Active'), ('Recent');
END; ELSE IF @FeatureSection = 'Problems' BEGIN
    INSERT INTO @FeatureToReport ([Name])
        VALUES
        ('Decommission'), ('Offline'), ('Refresh'), ('Ticketed'), ('In-Transit');
END; ELSE IF @FeatureSection = 'UserHelp' BEGIN
    INSERT INTO @FeatureToReport ([Name])
        VALUES
        ('Other');
END;

SELECT *
FROM COMPUTER C
WHERE C.Features IN (SELECT [Name] FROM @FeatureToReport);

that's pretty cool and I think will work! I'll have to test it out and let you know. Looking it over however I think it would work how I see it in my head! Thank you! — Mr Hobbits, Mar 08 '22 at 17:55
This is absolutely amazing and exactly what I was hoping to find. I just didn't know how to make it work. Thank you Dale!!! — Mr Hobbits, Mar 08 '22 at 18:20

score -1 · Answer 2 · answered Mar 04 '22 at 16:05

You can use dynamicSQL or just make it with a split function. Define your variable separate by , and use this function to split it in your IN

IN(SELECT ItemValue from dbo.fsplitToTable(@variable, ','))

Function

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fSplitToTable] (@PInStrSource varchar(8000) = NULL, @pInChrSeparator char(1) = '|')
RETURNS @ARRAY TABLE (ItemID INT, ItemValue VARCHAR(1000))
AS
BEGIN
    DECLARE @CurrentStr varchar(2000)
    DECLARE @ItemStr varchar(200)
    DECLARE @ItemID INT
    SET @CurrentStr = @PInStrSource
    SET @ItemID = 0
    WHILE Datalength(@CurrentStr) > 0
    BEGIN
        SET @ItemID = @ItemID+1
        IF CHARINDEX(@pInChrSeparator, @CurrentStr,1) > 0 
        BEGIN
            SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@pInChrSeparator, @CurrentStr,1) - 1)
            SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1, (Datalength(@CurrentStr) - CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1))
            INSERT @ARRAY (ItemID, ItemValue) VALUES (@ItemID,@ItemStr)
        END
        ELSE
        BEGIN
            INSERT @ARRAY (ItemID, ItemValue) VALUES (@ItemID,@CurrentStr)
            BREAK;
        END
    END
    RETURN
END

This is a terribly inefficient way to split a string in SQL Server; there are *far* better ways. SQL Server has had an in-built function since 2016, and there are plenty of examples of set based and CLR functions out there for those on older versions. — Thom A, Mar 04 '22 at 16:06
Also, in regards to Dynamic SQL methods, these very often create a very large SQL Injection vulnerability and should most certainly be avoided. — Thom A, Mar 04 '22 at 16:09
I don't think Dynamic SQL in this instance would be a particular SQL Injection Attack risk, but it will cause the server to not cache a query plan, or to cache a plan that is poor in some circumstances. — Aaron Reese, Mar 04 '22 at 16:23

SQL Query Where X IN swappable variable depending on search?

2 Answers2