CODEWITHSUNDEEP
sql-server
Cherven
Cherven

Reputation: 1131

Table-valued function, table variable and "Must declare scalar variable" error message

I am building a function that returns table result

ALTER FUNCTION [brm].[fnComputeScores_NEW]
(
    @var1 TINYINT
)
RETURNS 
@ret TABLE
(
    [producerid] INT
    ,[CityId] INT
    , CityName VARCHAR(100)
)
AS 
BEGIN


INSERT INTO @ret
        SELECT [producerid], [CityId] from producers

--placeholder

RETURN
END

everything is fine to this point

but code that I want to put in placeholder 

UPDATE @ret
SET
    CityName = Cities.Name
FROM
    @ret JOIN Cities
        ON @ret.CityId= Cities.CityId

generates compilation error

Must declare the scalar variable "@ret".

Why? How to fix it?

Upvotes: 3

Views: 7657

Answers (3)

David Walker
David Walker

Reputation: 101

You don't HAVE to use an alias; you can actually do the following: (The problem seems to be in the SQL parser). Notice the brackets around @ret.

UPDATE @ret
SET
    CityName = Cities.Name
FROM
    @ret JOIN Cities
        ON [@ret].CityId= Cities.CityId

Upvotes: 0

FistOfFury
FistOfFury

Reputation: 7125

I had the same problem as the OP. I had table variables in a function and I was getting the error 

"Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function"

Except, when I change the update statement to use the actual name of the table variable instead of the alias, it worked. The function was a multistatement function and the table variable was the result table (defined at the beginning of the function). Not sure why why it worked, but I would suggest trying that as a solution.

So for example, I changed:

update c
set column1=c.someValue+o.someValue
from @table as c inner join [otherTable] o on c.ID=o.ID

into:

update @table
set column1=c.someValue+o.someValue
from @table as c inner join [otherTable] o on c.ID=o.ID

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

You can't reference the table variable outside of FROM. This is not exclusive to UPDATE... from http://msdn.microsoft.com/en-us/library/ms175010.aspx:

Outside a FROM clause, table variables must be referenced by using an alias...

...so you can try:

UPDATE r
SET
    r.CityName = c.Name
FROM
    @ret AS r 
    INNER JOIN dbo.Cities AS c
    ON r.CityId = c.CityId;

Upvotes: 2

Related Questions