'Multiple' values for a variable

Question

A bit of background. There are multiple tables from multiple databases that have the same schemas. So, when I query to select all columns having the same master code (in the tables, the master code is in the column called CATMASTRCAT), the same code will have multiple rows, the only same thing about them is the CATMASTRCAT column. This works for a single master code (in the script below if I set the variable to 031325-002-70 it will show multiple rows having different organizations and same data with the rest, which is the desired result).

Question is, is there a way to have multiple master codes be as an input in the variable? I'm planning to create this as a stored procedure.

This is my SQL script:

DECLARE @ProductNumber AS VARCHAR(1000)
SET @ProductNumber = ('031325-002-70')

SELECT ITEMS
    ,ORGANIZATION
FROM [EU].[dbo].[SOMETHING14]
WHERE ITEMS in (@ProductNumber)

UNION

SELECT ITEMS
    ,ORGANIZATION
FROM [EU].[dbo].[SOMETHING12]
WHERE ITEMS in (@ProductNumber)


UNION

SELECT ITEMS
    ,ORGANIZATION
FROM [EU].[dbo].[SOMETHING11]
WHERE ITEMS IN (@ProductNumber)
   

Feel free to clarify any other needed data. I'm fairly new to SQL, just self-learning. You can also lecture me about the wrong code haha and how to do this better.

Thanks!

P.S. Attached the picture of query result

Answer 1

Yes the best way to do this is to use a table value parameter and then change the where clause to say

WHERE catmastrcat IN (SELECT catmastrcat FROM @tablevaluename)

or you could use an inner join -- which might be faster depending on indexes and other issues - the code for that would look like this

JOIN @tablevaluename tv ON AJF_CATMASTER.catmastrcat = tv.catmastrcat