Solved

SQL - Updating Variable Values

Posted on 2013-01-17
7
194 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]
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Damozz
Comment Utility
Thanks that worked, can you clarify?
0
 

Author Comment

by:Damozz
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article, I will show how to use the Ribbon IDs Tool Window to assign the built-in Office icons to a ribbon button.  This tool will help us to find the OfficeImageId that corresponds to our desired built-in Office icon. The tool is part of…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

743 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

16 Experts available now in Live!

Get 1:1 Help Now