Solved

How to assign Case Statements to sQL variables

Posted on 2013-12-16
4
285 Views
Last Modified: 2013-12-24
I am trying to assign Case statements to declared variables in a stored procedure, how do I do that?  Below is the stored proc:

USE [NuHL1]
GO
/****** Object:  StoredProcedure [dbo].[testsalcomp]    Script Date: 12/17/2013 01:53:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER Procedure [dbo].[testsalcomp]
AS
BEGIN
DECLARE @HousingComp as money
DECLARE @TransComp as money 
DECLARE @OtherComp as money

SELECT DISTINCT 
 EmpCpsPlnDta.CpsGrp
AS 'Comp Group',
EmpCpsPlnDta.Fld1
AS Unit,
EmpCpsPlnDta.Fld1Name
AS 'Unit Name',
(Emp.Lastname + ', ' + Emp.Firstname)
AS Name,
emp.emp as 'Emp Number',
Job.JobTitle,
EmpCpsPlnDta.RecAmt
AS 'Merit Increase Amount',
(AnnSal*ExchangeRate)
AS 'Current Annual Sal USD',

  (CASE WHEN
((select sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)) from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and sc.NewmnthlyamtCZ is not null AND SalComp='Housing')) IS NOT NULL
THEN
((select sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)) from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and sc.NewmnthlyamtCZ is not null AND SalComp='Housing'))
ELSE
((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and SalComp='Housing'))
END) 
AS 'Current New Housing Components (USD)',




(CASE WHEN
((select sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)) from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and sc.NewmnthlyamtCZ is not null AND SalComp='Other')) IS NOT NULL
THEN
((select sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)) from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and sc.NewmnthlyamtCZ is not null AND SalComp='Other'))
ELSE
((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and SalComp='Other'))
END)
AS 'Current New Other Components (USD)',

(CASE WHEN
((select sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)) from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and sc.NewmnthlyamtCZ is not null AND SalComp='Transportation')) IS NOT NULL
THEN
((select sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)) from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and sc.NewmnthlyamtCZ is not null AND SalComp='Transportation'))
ELSE
((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and SalComp='Transportation'))
END)
AS 'Current New Transportation Components (USD)',



((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and SalComp='Other'))
AS 'Current Other Components (USD)',
((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and SalComp='Transportation'))
AS 'Current Transportation Components (USD)',

((select sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)) from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null and sc.NewmnthlyamtCZ is not null))
AS 'New Transportation Components (USD)',

((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
AS 'Current Components (USD)',
((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
AS 'Current Components and Base (USD)',
((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
AS 'Planned Components (USD)',
((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
AS 'Planned Total Componens (USD)',
((eml.AnnlSal+EmpCpsPlnDta.RecAmt)*ExchangeRate)
AS 'Proposed New Base (Merit) Salary USD',
((isnull(RecAmt,'')*ExchangeRate)/((AnnSal*ExchangeRate)+(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
AS 'Merit Increase Pct',
(promoincreaseamt*ExchangeRate)
AS 'Promo Increase Amt USD',
(((isnull(RecAmt,'')*ExchangeRate)+(isnull(promoincreaseamt,'')*ExchangeRate))/((AnnSal*ExchangeRate)+(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
AS 'Total Increase Percent (Merit and Promo)',
RptLoc.RptLocName
AS 'Assigned Office',
Eml.Div
AS Region,

(case WHEN ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null)) > 0 
  then ((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate) + (promoincreaseamt*ExchangeRate)
  else ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate) + (EmpCpsPlnDta.RecAmt)
  end)
AS 'Planned Components and Base (USD)'
FROM
Emp
  LEFT JOIN Eml ON (Emp.Emp = Eml.Emp AND Eml.EmlEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL) AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL
  LEFT JOIN Job ON (Job.Job = Eml.Job AND Job.JobEfdDt IS NULL AND Job.JobEfdDt IS NULL) AND Job.JobEfdDt IS NULL
  LEFT JOIN Dpt ON (Dpt.Dpt = Eml.Dpt AND Dpt.Loc = Eml.Loc AND Dpt.DptEfdDt IS NULL AND Dpt.DptEfdDt IS NULL) AND Dpt.DptEfdDt IS NULL
  LEFT JOIN Org ON (Org.Org = Eml.Org AND Org.Dpt = Eml.Dpt AND Org.OrgEfdDt IS NULL AND Org.OrgEfdDt IS NULL) AND Org.OrgEfdDt IS NULL
  RIGHT JOIN Cty ON Cty.Cty = Emp.PrimCty AND Cty.CtyEfdDt IS NULL
  LEFT JOIN EmpCpsPlnDta AS EmpCpsPlnDta ON (EmpCpsPlnDta.Emp = Emp.Emp) AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL
  RIGHT JOIN RptLoc ON (RptLoc.RptLoc = Eml.RptLoc AND RptLoc.Cmp = Eml.Cmp AND RptLoc.RptLocEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL) AND RptLoc.RptLocEfdDt IS NULL
  RIGHT JOIN Cur ON Cur.Cur = Eml.Cur AND Cur.CurEfdDt IS NULL
  LEFT JOIN EmpSalCompTmp ON EmpSalCompTmp.Emp = Emp.Emp AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL
WHERE
((eml.empsts IN (N'Active',N'On Leave'))
AND
EmpCpsPlnDta.CpsPlnYr = N'2014'
AND
(EmpCpsPlnDta.Fld1 IN (N'IT ANZAME',N'IT Bus Servces',N'IT HelpDsk',N'IT Mgmt',N'IT SrvrHelpDsk',N'IT SteSuportUS',N'IT SysApps',N'IT UK')))
AND
(Emp.EmpEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Job.JobEfdDt IS NULL AND Dpt.DptEfdDt IS NULL AND Org.OrgEfdDt IS NULL AND Cty.CtyEfdDt IS NULL AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL AND Cur.CurEfdDt IS NULL AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL)
AND
Emp.Emp = 31415
ORDER BY
EmpCpsPlnDta.CpsGrp,
EmpCpsPlnDta.Fld1,
(Emp.Lastname + ', ' + Emp.Firstname)
END

Open in new window

0
Comment
Question by:VBBRett
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
ItWorked earned 125 total points
Comment Utility
Need to understand what you want to achieve?
You have declared variables but haven't specified where you want to use those?

You want to use it in case statement but with what logic?
it may be like

case @YourVariable when somevalue then here
        else not here
end

Open in new window


 or

case when yourColumn=@YourVariable then here
        else not here
end

Open in new window



Don't know what you're trying to achieve!!
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
Comment Utility
Dude, it is not helpful to dump a pile of T-SQL into a question, and effectively tell experts 'you guys figure out what is relevant, and what is not'.  Best to only post what is relevant to the question.

>I am trying to assign Case statements to declared variables in a stored procedure,
Do you mean like...
SELECT @some_variable = 
   CASE state_code = 'MI' THEN '5'
   CASE state_code='OR' AND country_code='USA' THEN '4'
   ELSE '3' END

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
Comment Utility
btw VBBRett - I wrote an article awhile back on SQL Server CASE solutions that will help you with a lot of these syntax questions.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
Comment Utility
I think you can do away with all (most?) of the sub-SELECTs -- the ((select ... from)) -- within the main SELECT by following the pattern below.



SELECT
DISTINCT
 EmpCpsPlnDta.CpsGrp
AS 'Comp Group',
EmpCpsPlnDta.Fld1
AS Unit,
EmpCpsPlnDta.Fld1Name
AS 'Unit Name',
(Emp.Lastname + ', ' + Emp.Firstname)
AS Name,
emp.emp as 'Emp Number',
Job.JobTitle,
EmpCpsPlnDta.RecAmt
AS 'Merit Increase Amount',
(AnnSal*ExchangeRate)
AS 'Current Annual Sal USD',
(eml.AnnlSal+EmpCpsPlnDta.RecAmt)*ExchangeRate)
AS 'Proposed New Base (Merit) Salary USD',
CASE WHEN [Current New Housing Components (USD) - Main] IS NOT NULL
     THEN [Current New Housing Components (USD) - Main] ELSE [Current New Housing Components (USD) - Alternate] END
     AS [Current New Housing Components (USD)],
...
FROM
Emp
  LEFT OUTER JOIN (
    SELECT
        sc.Emp,
        SUM(CASE WHEN sc.SalComp = 'Housing' AND sc.NewmnthlyamtCZ IS NOT NULL
                 THEN ROUND(sc.NewmnthlyamtCZ * c.ExchangeRate * 12, 2) END )
            AS 'Current New Housing Components (USD) - Main',
        ISNULL(
        SUM(CASE WHEN sc.SalComp = 'Housing'
                 THEN ROUND(sc.NewmnthlyamtCZ * c.ExchangeRate * 12, 2) END)
            , '') AS 'Current New Housing Components (USD) - Alternate',
        SUM(CASE WHEN sc.SalComp = 'Other' AND sc.NewmnthlyamtCZ IS NOT NULL
                 THEN ROUND(sc.NewmnthlyamtCZ * c.ExchangeRate * 12, 2) END )
            AS 'Current New Other Components (USD) - Main',
        ISNULL(
        SUM(CASE WHEN sc.SalComp = 'Other'
                 THEN ROUND(sc.NewmnthlyamtCZ * c.ExchangeRate * 12, 2) END )
            , '') AS 'Current New Other Components (USD) - Alternate',
        --...'Current New Transportation Components (USD) - Main' & '... - Alternate' --<<--!! code this like prev.
        ISNULL(
        SUM(CASE WHEN sc.SalComp = 'Other'
                 THEN SUM(ROUND(sc.amt * c.ExchangeRate, 2)) END )
            , '') AS 'Current Other Components (USD)',
        --...
    FROM dbo.EmpSalCompTmp sc
    INNER JOIN dbo.Cur c ON
        sc.AmtCur = c.Cur AND
        c.CurEfdDt IS NULL
    WHERE
        sc.EmpSalCompTmpEfdDt IS NULL AND
        sc.amt > 0
    GROUP BY
        sc.Emp
) AS sc_totals ON
    sc_totals.Emp = Emp.Emp
  LEFT JOIN ...
  ...

AS 'Proposed New Base (Merit) Salary USD',
...
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now