Table variables with calculated columns in UDFs bug?
-
Hello there,
found what may be a bug in SQL Server 2000 sp4, or maybe I'm just expecting too much of the system!
I have a UDF called ctfn_AMROC which returns a table variable @AMROC containing financial data.
CREATE FUNCTION ctfn_AMROC (@dtAMROCDate DATETIME,
@strClientName VARCHAR(60) = NULL,
@intProjectNumber INTEGER = NULL,
@strProjectManagerID VARCHAR(8) = NULL,
@strCellCode VARCHAR(15) = NULL)
RETURNS @AMROC TABLE
(ClientName varchar (30) NULL ,
JobNumber int NULL ,
ProjectNumber int NULL ,
TotalFee money NULL ,
Claims money NULL ,
Charges money NULL,
TotalAMROC AS ISNULL(TotalFee,0)-ISNULL(Charges,0))
AS
BEGIN ...etc etc...
As you can see, the last column in the table variable, TotalAMROC, is calculated. The function works correctly, returning the correct value of AMROC (Amount Recoverable on Charge) in the TotalAMROC column.
However, when I try and edit the function, I have noticed that EM has added a spurious line at the bottom of the function:
(isnull([TotalFee],0) - isnull([Charges],0))
which appears to be a copy of the TotalAMROC calculation.
I get this error message when I try and save the function...
Error 3729: Cannot alter 'ctfn_AMROC' because it is being reference by object 'ctfn_AMROC'
Deleting the offending line at the bottom of the function has no effect.
This only happens if I try and use calculated columns in the table variable. I have to edit the function by copying it into notepad, dropping it and pasting my amendments back in as a new function, which always compiles the first time, but thereafter fails with the above error message.
If I replace the definition of TotalAMROC with a standard datatype, I do not get the extra line or the error
TotalAMROC money NULL)
But then I have to do the calculation in my SQLStatement, which means subtracting two sub-selects.
I cannot find anything in BOL to say you cannot use calculated columns in a table variable. Am I simply expecting too much of SQL Server 2000?
David
If it ain't broke, don't fix it...
-
Hi,
does anyone have any thoughts on this, or have they been able to reproduce this effect?
David
If it ain't broke, don't fix it...
-
I have to admit I've not used calculations etc. in table variables, there are a number of "issues" with table variables which may or may not be a bug .. I assume your code works with a temp table.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply