Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

Declaring a variable in SQL and making it a field

I have a variable that I want to declare in SQL.  The whole idea is that it will compare two fields that are already being pulled from the database.  So the following is pseudo code to solve my sql dilemma:

newfield as float

if field1 > 0
newfield = field1
else
newfield = field2

How would I do the above in SQL and make newfield a field to be listed in a Query or stored procedure?  Thanks!
0
VBBRett
Asked:
VBBRett
  • 6
  • 5
  • 2
  • +2
2 Solutions
 
TheAvengerCommented:
That should give you a start:

declare @newfield float

select @newfield = case when field1 > 0 then field 1 else field2 end
from some_table
where ...

If you post more of your code I can help you integrate this in it.
0
 
Tony303Commented:
Here is a workup example.
DECLARE @Variable float

SET @Variable = 0

--TEST Variable works
SELECT @variable

--Put var in and test
IF (SELECT 12 AS field1) > 0
SET @variable = (SELECT 12 AS field1)
ELSE
SET @Variable = (SELECT -12 AS field2)


SELECT @Variable

Open in new window

0
 
virtuadeptCommented:
Lets say you have this table:

CREATE TABLE example (
   field1 int not null,
   field2 int not null
)

You could make a view against this table that would be:

SELECT field1, field2,
  case field1 > 0 then field1 else field2 end as newfield
FROM example


If the two fields are not the same data type, you convert to whichever type of the two can hold both values from the other, or to varchar if that isn't possible.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
no need for a variable, you can use a case statement

   select case when field1 > 0 then field1 else field2 end as MyField
0
 
Tony303Commented:
Here is how your example should work..

Cheers

DECLARE @Variable float


IF field1  > 0
SET @variable =field1
ELSE
SET @Variable = field2

SELECT @Variable

Open in new window

0
 
VBBRettAuthor Commented:
How am I to add this field into a stored procedure that already is built?  I keep getting incorrect syntax here and there.
0
 
virtuadeptCommented:
You are going to have to provide some specific example code for help with adding this into a stored procedure and if this stored procedure is being used by other things adding columns to it or changing what data shows up in some of the columns can and probably will break those things.
0
 
VBBRettAuthor Commented:
Should I post the query?
0
 
virtuadeptCommented:
One way you could do this without changing the stored procedure is this.

Create Table #proc_result_set (
--define all the columns names and data types of stored procedure result set
)

INSERT #proc_result_set
EXEC stored_procedure @parm1 = value, --etc.

SELECT
--columns list from the result set,
case field1 > 0 then field1 else field2 end as newfield
FROM #proc_result_set

This script could be converted to a wrapper stored proc if you must use stored procedures by adding a CREATE PROCEDURE new_proc_name (same parms as real proc) AS
at the start.
0
 
VBBRettAuthor Commented:
Well, here is the query

ALTER PROCEDURE [dbo].[Rpt_Comp]  
SELECT DISTINCT 
 EmpCpsPlnDta.CpsGrp
 AS 'Comp Group',
 EmpCpsPlnDta.Fld1
 AS Unit,
 EmpCpsPlnDta.Fld1Name
 AS 'Unit Name',
 (Emp.Lastname + ', ' + Emp.Firstname)
 AS Name,
 Job.JobTitle,
 EmpCpsPlnDta.RecAmt
 AS 'Merit Increase Amount',
 (AnnSal*ExchangeRate)
 AS 'Current Annual Sal 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)',
 ((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
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)
ORDER BY
 EmpCpsPlnDta.CpsGrp,
 EmpCpsPlnDta.Fld1,
 (Emp.Lastname + ', ' + Emp.Firstname)

Open in new window

0
 
VBBRettAuthor Commented:
The values that I want to compare are 'Current Components and Base (USD) and Planned Components USD.  If Planned Components USD is not 0 then I want the new field value to be the value of Planned Components USD...if the Planned Components USD is empty, then I wan the new field value to be the value of Current Components USD.
0
 
virtuadeptCommented:
Nevermind saw it.
0
 
virtuadeptCommented:
Added "NewField" you probably want to pick a better name. Also keep in mind what I said about adding columns to procs breaking other things that use the proc.

ALTER PROCEDURE [dbo].[Rpt_Comp]  
SELECT DISTINCT 
 EmpCpsPlnDta.CpsGrp
 AS 'Comp Group',
 EmpCpsPlnDta.Fld1
 AS Unit,
 EmpCpsPlnDta.Fld1Name
 AS 'Unit Name',
 (Emp.Lastname + ', ' + Emp.Firstname)
 AS Name,
 Job.JobTitle,
 EmpCpsPlnDta.RecAmt
 AS 'Merit Increase Amount',
 (AnnSal*ExchangeRate)
 AS 'Current Annual Sal 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)',
 ((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.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))
	else ((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)
	end
) as NewField 
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)
ORDER BY
 EmpCpsPlnDta.CpsGrp,
 EmpCpsPlnDta.Fld1,
 (Emp.Lastname + ', ' + Emp.Firstname)

Open in new window

0
 
virtuadeptCommented:
I just did a minor edit to the prior post, I forgot "when" after the case keyword.
0
 
VBBRettAuthor Commented:
Hi virtuadept...I caught the When error prior to your posting but I appreciate you helping the cause.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now