Re: Stored Procedure CASE

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 10/04/04 

Date: Mon, 4 Oct 2004 15:59:00 -0400

Yeah, I wasn't sure what your exact business requirement was. Glad it
worked. 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Drew" <dlaing@NOswvtc.state.va.SPAMus> wrote in message
news:uYrEDukqEHA.1952@TK2MSFTNGP12.phx.gbl...
PERFECT!!! Although I had to change it a little bit, but thanks for the
format!
WHERE  (E.EmpActive = 1 AND @Status = 1
AND    E.EmpStatus in ('Permanent', 'Part Time'))
OR     (E.EmpActive = 1 AND @Status = 2
AND    E.EmpStatus = 'Part Time Classified')
Thanks,
Drew Laing
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:%23B7GOdjqEHA.3700@TK2MSFTNGP15.phx.gbl...
> Dang!:
>
> WHERE  (E.EmpActive = 1 AND @Status = 1
> AND    E.EmpStatus in ('Permanent', 'Part Time'))
> OR     (E.EmpActive = 2AND @Status = 2
> AND    E.EmpStatus 'Part Time Classified')
>
>
> -- 
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:uY080WjqEHA.2340@TK2MSFTNGP11.phx.gbl...
> Try:
>
> WHERE  (E.EmpActive = 1
> AND    E.EmpStatus in ('Permanent', 'Part Time'))
> OR     (E.EmpActive = 2
> AND    E.EmpStatus 'Part Time Classified')
>
>
> -- 
> Tom
>
> ---------------------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
>
> "Drew" <dlaing@NOswvtc.state.va.SPAMus> wrote in message
> news:u7oNsOjqEHA.3288@TK2MSFTNGP12.phx.gbl...
> I need to edit an existing stored procedure so that it will find the
> correct
> records.  This is the current SP that is being used by the facility for
> reporting.
>
> CREATE PROCEDURE spTKMemo
> @Status  varchar(15)
> AS
> SELECT     TOP 100 PERCENT E.EmpID, E.EmpFName, E.EmpMName, E.EmpLName,
> EP.EmpSSN, D.DeptName, C.CostCenter, P.PosTimeKeeper, T.TimeKeeper,
>                      EF.EmpDirectDeposit, DD.DeptName AS DisplayDeptName
> FROM         EmpCore.dbo.tblEmployee E INNER JOIN
>                      EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID =
> EP.EmpID INNER JOIN
>                      EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER
> JOIN
>                      EmpCore.dbo.tblDept D ON P.PosDeptID = D.DeptID INNER
> JOIN
>                      EmpCore.dbo.tblCostCenter C ON P.PosCostCenter =
> C.CostCenterID INNER JOIN
>                      EmpCore.dbo.tblTimeKeepers T ON P.PosTimeKeeper =
> T.TimeKeeperID INNER JOIN
>          EmpCore.dbo.tblDept DD ON T.Dept = DD.DeptID INNER JOIN
>                      dbo.tblEmpInfo EF ON E.EmpID = EF.EmpID
> WHERE     (E.EmpActive = 1) AND (E.EmpStatus = CASE WHEN @Status = 1 THEN
> 'Permanent' ELSE 'Part Time'
>         END)
> ORDER BY E.EmpLName
> GO
>
> So if the user runs the SP and @Status = 1 then it selects all the
> PERMANENT
> employees, otherwise it selects the parttimers.  This works and works
> well,
> but now I have to add a Part Time Classified to it.  So I need it to take
> the user input 1 or 2, if it is a 1 I need it to select the PERM and Part
> Time Classified employees.
>
> I tried this,
>
> CREATE PROCEDURE spTKMemo
> @Status  varchar(15)
> AS
> SELECT     TOP 100 PERCENT E.EmpID, E.EmpFName, E.EmpMName, E.EmpLName,
> EP.EmpSSN, D.DeptName, C.CostCenter, P.PosTimeKeeper, T.TimeKeeper,
>                      EF.EmpDirectDeposit, DD.DeptName AS DisplayDeptName
> FROM         EmpCore.dbo.tblEmployee E INNER JOIN
>                      EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID =
> EP.EmpID INNER JOIN
>                      EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER
> JOIN
>                      EmpCore.dbo.tblDept D ON P.PosDeptID = D.DeptID INNER
> JOIN
>                      EmpCore.dbo.tblCostCenter C ON P.PosCostCenter =
> C.CostCenterID INNER JOIN
>                      EmpCore.dbo.tblTimeKeepers T ON P.PosTimeKeeper =
> T.TimeKeeperID INNER JOIN
>          EmpCore.dbo.tblDept DD ON T.Dept = DD.DeptID INNER JOIN
>                      dbo.tblEmpInfo EF ON E.EmpID = EF.EmpID
> WHERE     (E.EmpActive = 1) AND (E.EmpStatus = CASE WHEN @Status = 1 THEN
> 'Permanent' OR E.EmpStatus = 'Part Time Classified'
>              ELSE 'Part Time'
>         END)
> ORDER BY E.EmpLName
> GO
>
> but that errors out on the Permanent.  Is there anyway to do this?
>
> Thanks,
> Drew Laing
>
>

Relevant Pages

  • Re: Stored Procedure CASE
    ... Columnist, SQL Server Professional ... Drew Laing "Tom Moreau" wrote in message ... > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA> SQL Server MVP ... >> C.CostCenterID INNER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedure CASE
    ... SQL Server MVP ... Columnist, SQL Server Professional ... EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER JOIN ... 'Permanent' ELSE 'Part Time' ...
    (microsoft.public.sqlserver.programming)
  • Re: opposite of a inner join?
    ... SQL Server MVP ... Columnist, SQL Server Professional ... "Joe Scalise" wrote in message ... table to the INNER JOIN. ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedure CASE
    ... Columnist, SQL Server Professional ... EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER JOIN ... 'Permanent' ELSE 'Part Time' ... Drew Laing ...
    (microsoft.public.sqlserver.programming)
  • Re: WHERE clause applies to right-hand table of LEFT JOIN
    ... it's always best to specify INNER JOIN ... T1_PK int NOT NULL ... CONSTRAINT FK_T2_T1 FOREIGN KEY ... The SQL Server cost-based optimizer is very good at generating efficient ...
    (microsoft.public.sqlserver.server)
Loading