SQL Server – How to Set Variables to Multiple Integer Values

sql serversql-server-2008

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

Best Answer

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.

Related Question