SQL Query Where X IN swappable variable depending on search?-CodePudding

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.

CodePudding user response：

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);

CodePudding user response：

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