RE: Stored Procedure with Multiple conditions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 07:53:10 -0800

Take your time reading these outstanding articles written by Erland
Sommarskog, there you will find the answer to your question.

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

AMB

"Drew" wrote:

> I am using Access to report out of my SQL Server database. I am building
> stored procedures and then using them for the reports. Everything is
> working fine, but I have hit a snag. I need to build a report to show
> employee information. I have 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
>
> As you can see it takes the Social Security Number and filters the database
> with that value. I need to modify this SP to make it allow up to 10 SSN
> entries. How do I go about doing this? The problem is, there may be 10
> entries, and then there may only be 1 entry.
>
> Thanks,
> Drew Laing
>
>
>

Relevant Pages

  • Re: speeding up query calculation..
    ... TransNum 2197 entries, 3 pages, 127 values ... End inputs to Query - ... Inner Join table 'tblTransaction' to table 'tblTransDetail' ...
    (microsoft.public.access.queries)
  • Re: Nested Iif in View / Stored Procedure
    ... "Candace" wrote in message ... > FROM dbo.Device INNER JOIN ... >>IIF statements become CASE statements in SQL Server ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Re: List parameters for Stored Procedures
    ... INNER JOIN sys.systypes st ON st.xtype = p.system_type_id ... OP said he wants "to get a list of stored procedures (not system ... I think this is possible by querying the sysobjects table and the ... syscolumns has parameters of procedures. ...
    (microsoft.public.sqlserver.programming)
  • Re: Nested Iif in View / Stored Procedure
    ... datetime, @End_Date datetime, @Nursing_Unit nvarchar, ... FROM dbo.Device INNER JOIN ... >IIF statements become CASE statements in SQL Server ...
    (microsoft.public.access.adp.sqlserver)
  • Re: creating a report from a recordset
    ... reports run off this table just fine. ... displayed in a msgbox as the variable strSQL: ... FROM ((Employees INNER JOIN tblOrg ON Employees.OrgID = tblOrg.OrgID) INNER ... >> MsgBox strSQL ...
    (microsoft.public.access.reports)