Solved

How to assign Case Statements to sQL variables

Posted on 2013-12-16
4
296 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
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 65

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 65

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

776 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