Geek asks and answers

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

I create a function that returns the result of a table

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

all right up to this point

but the code that I want to put in placeholder

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

generates a compilation error

Must declare the scalar variable "@ret".

Why? How to fix it?

+3
source share
3 answers

You cannot reference a table variable outside FROM. This does not apply to UPDATE... from http://msdn.microsoft.com/en-us/library/ms175010.aspx :

Outside of the FROM clause, table variables must be referenced 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;
+2
source

, OP. ,

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

, update, , . , - ( ). , , .

, , :

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


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

You do not need to use an alias; you can do the following: (The problem seems to be in the SQL parser). Note the brackets around @ret.

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

More articles:


All Articles