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 :
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.