Solved

Declaring a variable in SQL and making it a field

Posted on 2013-12-12
15
338 Views
Last Modified: 2013-12-12
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
Comment
Question by:VBBRett
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 20

Expert Comment

by:TheAvenger
ID: 39714941
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
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 100 total points
ID: 39714944
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
 
LVL 8

Expert Comment

by:virtuadept
ID: 39714945
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
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39714947
no need for a variable, you can use a case statement

   select case when field1 > 0 then field1 else field2 end as MyField
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39714950
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
 

Author Comment

by:VBBRett
ID: 39715052
How am I to add this field into a stored procedure that already is built?  I keep getting incorrect syntax here and there.
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39715067
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:VBBRett
ID: 39715080
Should I post the query?
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39715085
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
 

Author Comment

by:VBBRett
ID: 39715091
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
 

Author Comment

by:VBBRett
ID: 39715096
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
 
LVL 8

Expert Comment

by:virtuadept
ID: 39715100
Nevermind saw it.
0
 
LVL 8

Accepted Solution

by:
virtuadept earned 400 total points
ID: 39715120
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
 
LVL 8

Expert Comment

by:virtuadept
ID: 39715125
I just did a minor edit to the prior post, I forgot "when" after the case keyword.
0
 

Author Comment

by:VBBRett
ID: 39715385
Hi virtuadept...I caught the When error prior to your posting but I appreciate you helping the cause.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

13 Experts available now in Live!

Get 1:1 Help Now