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