Solved

How to assign Case Statements to sQL variables

Posted on 2013-12-16
4
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
ItWorked earned 125 total points
ID: 39723362
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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39723800
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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39724088
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:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 39724971
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Copy Database Wizard 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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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