Thursday, March 8, 2012

"Must declare the scalar variable" in table-valued function

Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@.dateStart DATETIME,
@.dateEnd DATETIME
)
RETURNS @.arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @.arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @.arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = @.arTemp.Insurer AND
Pims.NABP = @.arTemp.NABP AND
Pims.NumRx = @.arTemp.NumRx
);

INSERT INTO @.arDetail SELECT * FROM @.arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@.arTemp".

I don't understand why SQL thinks @.arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

Not sure of the why, but aliasing the table variable seems to satisfy the compiler.

Code Snippet

UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = t1.Insurer AND
Pims.NABP = t1.NABP AND
Pims.NumRx = 1.NumRx
)

FROM @.arTemp t1;

|||Immediately after posting that I found the workaround -- enclose @.arTemp in brackets where it is used to qualify a column.

So
ALTER FUNCTION MakeArDetail
(
-- Add the parameters for the function here
@.dateStart DATETIME,
@.dateEnd DATETIME
)
RETURNS @.arDetail TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @.arTemp TABLE
(
Insurer VARCHAR(50),
NABP INT DEFAULT 0,
Claim MONEY DEFAULT 0,
Payment MONEY DEFAULT 0,
NumRx CHAR(7),
PatientName VARCHAR(50),
Paid030 MONEY DEFAULT 0,
Paid3160 MONEY DEFAULT 0,
Paid6190 MONEY DEFAULT 0,
Paid91120 MONEY DEFAULT 0,
Paid121 MONEY DEFAULT 0
)

INSERT INTO @.arTemp
SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @.arTemp SET Claim =
(SELECT SUM(Pims.AmtReq)
FROM Pims
WHERE Pims.Insurer = [@.arTemp].Insurer AND
Pims.NABP = [@.arTemp].NABP AND
Pims.NumRx = [@.arTemp].NumRx
);

INSERT INTO @.arDetail SELECT * FROM @.arTemp
RETURN
END
GO

works -- at least it passes the syntax check.
I think this is a SQL bug.

No comments:

Post a Comment