How to set variables to multiple values (that are integers)?

Question

I have 5 facilities in my division, but the code below in a stored procedure only pulls documents when the "facilityID = 4" unless if I change it manually to represent other ID numbers. So I need the facilityID variable below to point to more than one value like: [Set @FacilityID = 1,2,3,4,5] but of course it's not allowing me. If it was characters, I would be able to put them in quotes. Any advice please?

declare @facilityID tinyint,
@facilitydivisionID tinyint,
SET @facilityID = 4
SET @facilitydivisionID = 6

Answer 1

You can use a table-valued parameter.

CREATE TYPE dbo.Facilities AS TABLE
(
 FacilityID TINYINT PRIMARY KEY
);

Then your stored procedure can say:

CREATE PROCEDURE dbo.whatever
 @FacilityDivisionID TINYINT,
 @Facilities dbo.Facilities READONLY
AS
BEGIN
 SET NOCOUNT ON;
 SELECT ... FROM dbo.wherever AS w
 INNER JOIN @Facilities AS f
 ON f.FacilityID = w.FacilityID
 WHERE w.FacilityDivisionID = @FacilityDivisionID;
END
GO

Then you can call it from T-SQL like this:

DECLARE @t dbo.Facilities;
INSERT @t VALUES(1),(2),(3),(4),(5);
DECLARE @d TINYINT = 6;
EXEC dbo.whatever @FacilityDivisionID = @d, @Facilities = @t;

Or from C# using a DataTable and a parameter type as Structured. You can search to find plenty of examples of using TVPs from application code.

• Shayma Ahmad: Thank you, thats great as I didnt know that I could declare a variable that way by pointing it to a table as a readonly. Since I have the "Facilities" table already in my database that stores the 5 facilitie's information based on the facilityID, then I have to create the Table Valued Parameter with a different name, correct? Thanks in advance
• Aaron Bertrand: Yes, you will need a different name.
• Shayma Ahmad: I keep getting error 137 that the scalar variable needs to be declared, although I declared it as a read only like you showed me above, not sure what am I missing? I will attach the code once I figuer out how to do this here, in my select statement in the stored procedure: I have multipple joins with other tables. Thanks again.
• Shayma Ahmad: I'm looking at the help for the code block, but I still dont understand how to post my code here, as Indenting four spaces or writing <pre> <code> infront of my code will edit it like the way you have above, please advice, thanks
• Aaron Bertrand: @Shayma please don't post code in a comment. If you have further problems getting your new solution to work, please post a new question.