Building report with Stored Procedure that takes mulitple parameters

[Drew]

I have built a report for showing Employee Information. It runs off the
following SP,

CREATE PROCEDURE spEmpInfo
@EmpSSNEntry varchar(15),
AS
IF @EmpSSNEntry IS NULL
SELECT E.EmpID, P.PosID, E.EmpFName, E.EmpMName, E.EmpLName,
E.EmpSuffix, E.EmpShift, E.EmpStatus, E.EmpActive, P.PosRoleCode,
P.PosTimeKeeper, C.CostCenter, EF.EmpSalary,
EF.EmpDirectDeposit, EF.EmpInsID, EP.EmpRace, EP.EmpSex, EP.EmpDOB,
EP.EmpStateBegDate,
EP.EmpSWVTCBegDate, EP.EmpSWVTCSepDate,
EP.EmpPositionBegDate, EP.EmpPositionSepDate, EP.EmpSSN, EP.EmpAddress,
EP.EmpOtherAddress, EP.EmpCity, EP.EmpState,
EP.EmpZip, EP.EmpSepReason
FROM EmpCore.dbo.tblEmployee E INNER JOIN
EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER
JOIN
EmpCore.dbo.tblCostCenter C ON P.PosCostCenter =
C.CostCenterID INNER JOIN
dbo.tblEmpInfo EF ON E.EmpID = EF.EmpID INNER JOIN
EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID =
EP.EmpID
WHERE (E.EmpActive = 1)
ELSE
SELECT E.EmpID, P.PosID, E.EmpFName, E.EmpMName, E.EmpLName,
E.EmpSuffix, E.EmpShift, E.EmpStatus, E.EmpActive, P.PosRoleCode,
P.PosTimeKeeper, C.CostCenter, EF.EmpSalary,
EF.EmpDirectDeposit, EF.EmpInsID, EP.EmpRace, EP.EmpSex, EP.EmpDOB,
EP.EmpStateBegDate,
EP.EmpSWVTCBegDate, EP.EmpSWVTCSepDate,
EP.EmpPositionBegDate, EP.EmpPositionSepDate, EP.EmpSSN, EP.EmpAddress,
EP.EmpOtherAddress, EP.EmpCity, EP.EmpState,
EP.EmpZip, EP.EmpSepReason
FROM EmpCore.dbo.tblEmployee E INNER JOIN
EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER
JOIN
EmpCore.dbo.tblCostCenter C ON P.PosCostCenter =
C.CostCenterID INNER JOIN
dbo.tblEmpInfo EF ON E.EmpID = EF.EmpID INNER JOIN
EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID =
EP.EmpID
WHERE (E.EmpActive = 1) AND EP.EmpSSN = @EmpSSNEntry
GO

I need to make this stored procedure have the ability to take up to 10 SSN
at once to return 10 records. There is a limit of 10, but there could only
be 1 SSN entered.

Does anyone know how I can go about doing this?

Thanks,
Drew Laing

 

[Sylvain Lafontaine]

Add 9 parameters @EmpSSNEntry1, ....9 and associated them to your query with
an OR or an UNION and you should be ok. You can also set these SSN into a
table and use this table for a Join and a Where.

Obviously, maybe there is a better idea than an OR or an UNION to do this
but you should give us a résumé of your query; with only the relevant fields
displayed so that we can understand it.

S. L.

 

[Drew]

I accomplished this, a whole lot easier than I thought... Just added the 10
parameters to the query with OR operator. Then, I can pass 1 SSN and the
rest empties, to the query. This query isn't used a whole lot, so the
inefficiencies are not a big deal.

Thanks,
Drew