Solved

SQL - Updating Variable Values

Posted on 2013-01-17
7
195 Views
Last Modified: 2013-01-17
Please can someone advise if it is possible to update a variable after the initial SET statement.  I want the variable to be updated with a single value based on several other values added together.

SET @TotalDuration = (SELECT Averages.Repair + Averages.Invoiced FROM Averages)

Receiving "Incorrect syntax near SET..."

The variable will then be used in a subsequent query as opposed to repeating the calculation over and over.
0
Comment
Question by:Damozz
  • 4
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38787240
that syntax IS correct inside T-SQL in MS SQL Server.
please clarify where/how you are using this?
did you dod the DECLARE variable?
please provide more of the code ...
0
 

Author Comment

by:Damozz
ID: 38787274
The averages table is a CTE... so I'm trying to set the variable immediately after

DECLARE @TotalDuration INT;

WITH

AVERAGES (Repair, Invoiced) AS

(
SELECT .....


)

SET @TotalDuration = (SELECT Averages.Repair + Averages.Invoiced FROM Averages)

The query works until I add the SET variable line which I intend to use for other calculations....
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38787392
then it needs to be a SELECT and not a SET:
DECLARE @TotalDuration INT;
WITH
AVERAGES (Repair, Invoiced) AS
(
SELECT .....

)
SELECT @TotalDuration = Averages.Repair + Averages.Invoiced FROM Averages)

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Damozz
ID: 38787466
Thanks that worked, can you clarify?
0
 

Author Comment

by:Damozz
ID: 38787522
Does this prevent me from referencing the Averages CTE after this statement

e.g.

SELECT Averages.Repair/@TotalDuration

Also should the closing bracket on the above solution (line 8) be removed or is there an opening bracket missing?

Thanks
0
 

Author Comment

by:Damozz
ID: 38787614
Sorry the message I now receive is

Msg 208, Level 16, State 1, Line 158
Invalid object name 'Averages'.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38787868
you can use the CTE only once.
but you can use it like this:

DECLARE @TotalDuration INT;
DECLARE @repairs INT
WITH
AVERAGES (Repair, Invoiced) AS
(
SELECT .....

)
SELECT @TotalDuration = a.Repair + a.Invoiced 
   , @repairs = a.Repaid
  FROM Averages a

select @totalduration
   , cast(@repairs as decimal(10,2)) / @totalduration                                            

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article shows how to make a Windows 7 gadget that extends its U/I with a flyout panel -- a window that pops out next to the gadget.  The example gadget shows several additional techniques:  How to automatically resize a gadget or flyout panel t…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 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

22 Experts available now in Live!

Get 1:1 Help Now