Hi Guys,
I've got a question about reusing table data but a view won't work in this scenario as I have a parameter that needs to be passed in. Basically this part of the system requires a travellerid to be sent to the procedure and a list of arrangers returned for that specific traveller. There are around 7 business rules that are used to determine which arrangers can be returned and they are mutually exclusive, so in order to accommodate these optional rules I have used a series of UNIONS inside a derived query. This is working well, and the performance seems good across a fairly large database, however I need to reuse these rules (UNIONS) in about 4 other parts of the system.
I initially tried to create a VIEW with these UNIONS but that didn't work due to the differing logic in each UNION and different parameter requirements, so I was thinking maybe a function could solve this issue? If I created a function that took @travellerid as a param and returned a list of arrangerid based on the business rules, would this be an ideal/fast solution? I am currently using UNION ALL and a DISTINCT in the outer query as this proved much faster than using UNION's for the uniqueness of data.
Current Procedure with business rules below (SQL Server 2008):
CREATE PROCEDURE [dbo].[getArrangersForTraveller]
@travellerid int
AS
DECLARE @costcentreid int
DECLARE @departmentid int
-- Shorthand the traveller costcentre and department for use in queries below
SET @costcentreid = (SELECT costcentreid FROM traveller WHERE id = @travellerid)
SET @departmentid = (SELECT departmentid FROM traveller WHERE id = @travellerid)
SELECT DISTINCT t.id, t.firstname, t.lastname, ti.name AS title, dv.preferred
FROM traveller t
INNER JOIN title ti ON t.titleid = ti.id
INNER JOIN
(
-- Get Preferred Arrangers linked to Department Groups
SELECT dg.arrangerid as id
FROM departmentGroup dg
INNER JOIN department_departmentGroup ddg
ON (dg.id = ddg.departmentGroupId AND ddg.departmentid = @departmentid)
UNION ALL
-- Get Preferred Arrangers linked to Cost Centre Groups
SELECT cg.arrangerid as id
FROM costCentreGroup cg
INNER JOIN costcentre_costCentreGroup ccg
ON (cg.id = ccg.costCentreGroupId AND ccg.costcentreid = @costcentreid)
UNION ALL
-- If Cost Centre Group has a linked department and this department matches
-- the travel arrangers department then return these travel arrangers as well
SELECT t3.id
FROM costCentreGroup cg1
INNER JOIN costcentre_costCentreGroup ccg1
ON (cg1.id = ccg1.costCentreGroupId AND ccg1.costcentreid = @costcentreid)
INNER JOIN traveller t3
ON t3.departmentid = cg1.departmentid
WHERE t3.accesslevelid > 1
UNION ALL
-- Get Direct linked travel arrangers
SELECT t1.travelarrangerid as id
FROM travelarranger_traveller t1
WHERE t1.travellerid = @travellerid
UNION ALL
-- Get Cost Centre linked arrangers
SELECT tc.travelarrangerid as id
FROM travelArranger_costcentre tc
WHERE tc.costcentreid = @costcentreid
UNION ALL
-- Get Department linked arrangers
SELECT td.travelarrangerid
FROM travelArranger_department td
WHERE td.departmentid = @departmentid
UNION ALL
-- Get Company flagged arrangers
SELECT t2.id
FROM traveller t2
INNER JOIN company c ON t2.companyid = c.id
WHERE t2.accesslevelid > 1
AND ((c.allowTravelArrangerDepartmentAccess = 1 AND t2.departmentid = @departmentid)
OR (c.allowTravelArrangerCostCentreAccess = 1 AND t2.costcentreid = @costcentreid))
) as dv ON dv.id = t.id
WHERE t.accessLevelid > 1 -- arranger or manager
AND t.isenabled = 1
ORDER BY dv.preferred DESC, t.lastname, t.firstname;