Making a variable that is a list, and using null as an if/else selector

Question

I want to create a variable that holds a list of UPCs that we need to search over. Instead of using:

select * from foo
    where upc in (3410015215, 3410015217, 3410015243)

I want to say something like:

Declare @UPCList    Varchar(255)    = (3410015215, 3410015217, 3410015243)

--If you want to check all stores, swap the commentation status of the next two lines
--Declare @Store        int             = NULL
  Declare @Store        int             = 203

My second issue is, I want to search fleem for a specific store, but if someone wants to search all stores, then they should be able to make @Store equal to NULL .

select * from foo p
inner join fleem f on p.upc = f.upc_id
if (@Store is NULL)
    begin
        where f.BOH = 1
    end
else (@Store is not NULL)
    begin
        where f.store = @Store AND f.BOH = 1
    end

Are these things possible? If so, what is the best way to go about doing them?

Reading suggestion to understand options and evaluate "best" - kitchen sink query — SMor
– SMor, Commented Jul 14, 2021 at 17:11

David Kidwell · Accepted Answer · 2021-07-14 19:09:39Z

2

There is no 'list' type in SQL. In SQL a list would just be a table or a temporary table with a single column and however many rows.

To solve your first issue you could store the values in the temporary table and then join against that to create the filter you are looking for.

CREATE TABLE #MyList (
  upcs VARCHAR(MAX)
)

INSERT INTO #MyList (upcs)
VALUES
(123),
(456),
(789)

And then use #MyList in an inner join with foo,

SELECT * FROM foo AS f
INNER JOIN #MyList AS ml
ON f.upcs = ml.upcs

Then for your second issue all you need to do is include a WHERE clause with a CASE statement after your join,

select * from foo p
inner join fleem f on p.upc = f.upc_id
WHERE f.BOH = CASE 
  WHEN @Store IS NULL THEN f.BOH
  ELSE @Store 
END

edited Jul 14, 2021 at 19:09

answered Jul 14, 2021 at 16:57

David Kidwell

7201 gold badge7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve Lovell · Accepted Answer · 2021-07-14 17:02:55Z

1

Pretty sure this should really be two separate questions, but here are my answers. I'm assuming you're using SQL Server, but I could be wrong as you haven't tagged a DBMS. The code below may work on other systems, but I don't make any promises:

Q1: Table Variables

You can use table variables to do the sort of thing you've asked in your first question. See the code below.

DECLARE @UPCList as TABLE (upc int)
INSERT INTO @UPCList
VALUES
(3410015215),
(3410015217),
(3410015243)

After this you can do with @UPCList all the things you could do with an ordinary table.

Q2: Set Logic

You're not allowed to use IF/THEN inside a query. However, the logic you've described is easy to turn into a standard WHERE clause.

SELECT
    * 
FROM
    foo p
    JOIN fleem f on p.upc = f.upc_id
WHERE
    f.boh = 1
    AND (f.store = @Store 
         OR @Store is NULL)

answered Jul 14, 2021 at 17:02

Steve Lovell

2,5742 gold badges15 silver badges18 bronze badges

1 Comment

GokuMizuno Over a year ago

Yes, it is a Microsoft SQL server. Thank you; I have added the tag.

Collectives™ on Stack Overflow

Making a variable that is a list, and using null as an if/else selector

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related