asked on

SQL - Updating Variable Values

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.

Guy Hengel [angelIII / a3]

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 ...

Damozz

ASKER

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....

Guy Hengel [angelIII / a3]

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

Damozz

ASKER

Thanks that worked, can you clarify?

Damozz

ASKER

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

Damozz

ASKER

Sorry the message I now receive is

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial