CODEWITHSUNDEEP
sql-server
Mr.Glaurung
Mr.Glaurung

Reputation: 596

Get value from the select statement on the row above in T-SQL

I want to get the value the previous select statement returned (similar to the @@IDENTITY function)

Code:

IF EXISTS (SELECT MyCriticalValue
           FROM Units u
           JOIN pUnit pu
             ON pu.UnitID = u.UnitID
           WHERE u.ChildUnitID = ( SELECT ps.UnitID
                                   FROM psUnit ps
                                   WHERE ps.Code = @CurrentCode)
            AND pu.Type = 117
            AND u.TypeID = 1 )
BEGIN
  SET @CriticalValue = @@identity
END

I know that @@IDENTITY doesn't work since that's only for inserts. Is there a similar function to achieve what I want (which is to get "MyCriticalValue" from the select statement into the variable @CriticalValue without typing the entire SELECT statement twice).

EDIT:

Clarification: When I come to this point in my SQL script @CriticalValue is already set and I should ONLY overwrite it if the select-statement returns anything, so it should not be overwritten in any other case.

Upvotes: 3

Views: 86

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

SELECT @CriticalValue = ISNULL(MyCriticalValue, @CriticalValue)
FROM Units u
JOIN pUnit pu
 ON pu.UnitID = u.UnitID
WHERE u.ChildUnitID = (
                        SELECT ps.UnitID
                        FROM psUnit ps
                        WHERE ps.Code = @CurrentCode
                      )
  AND pu.Type = 117
  AND u.TypeID = 1;

IF @CriticalValue IS NOT NULL
...

I assumed that your query return one value.

Upvotes: 4

Related Questions