T-Sql syntax error in Update from Select

Question

I have a stored procedure in T-Sql with this code at the end of it:

UPDATE @Results
SET Percentage = CASE B.Total
WHEN 0 THEN 0
ELSE (CAST(Number AS FLOAT)/B.Total)
END
FROM @TotalAnswersPerQuestion AS B
WHERE @Results.QuestionNumber=B.QuestionNumber

The temp table @Results is defined and correctly used in this store procedure just before these instructions, but I get an error on the last line saying:

Msg 137, Level 15, State 2, Procedure Report, Line 111 Must declare the scalar variable "@Results".

This are my tables:

DECLARE @TotalAnswersPerQuestion Table
(QuestionNumber int,
Total int)

DECLARE @Results Table
(
    QuestionNumber int,
    QuestionTitle varchar(max),
    AnswerNumber int,
    AnswerLable varchar(max),
    ProfileGroupID int,
    [Name] varchar(255),
    Identifier varchar(20),
    Number int,
    Percentage float
)

What's wrong?

Dave Markle · Accepted Answer · 2010-02-04 12:15:27Z

4

UPDATE r
SET Percentage = 
    CASE B.Total
        WHEN 0 THEN 0
        ELSE (CAST(Number AS FLOAT)/B.Total)
    END
FROM @TotalAnswersPerQuestion AS B
INNER JOIN @Results r
ON r.QuestionNumber=B.QuestionNumber;

I am shameless about UPDATE FROM! Let the flaming begin!

answered Feb 4, 2010 at 12:15

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quassnoi · Accepted Answer · 2010-02-04 12:16:54Z

1

Alias the table:

UPDATE r
SET Percentage = CASE B.Total
WHEN 0 THEN 0
ELSE (CAST(Number AS FLOAT)/B.Total)
END
FROM   @Results r
JOIN   @TotalAnswersPerQuestion AS B
ON     r.QuestionNumber=B.QuestionNumber

answered Feb 4, 2010 at 12:16

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Comments

Kamal · Accepted Answer · 2010-02-04 12:18:59Z

0

UPDATE r
SET Percentage = CASE B.Total
WHEN 0 THEN 0
ELSE (CAST(Number AS FLOAT)/B.Total)
END
FROM @Results r
,@TotalAnswersPerQuestion AS B
WHERE r.QuestionNumber=B.QuestionNumber

answered Feb 4, 2010 at 12:18

Kamal

2,5222 gold badges35 silver badges48 bronze badges

Collectives™ on Stack Overflow

T-Sql syntax error in Update from Select

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related