Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table variables with calculated columns in UDFs bug? Expand / Collapse
Author
Message
Posted Tuesday, February 7, 2006 5:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32

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...
Post #256329
Posted Wednesday, February 8, 2006 7:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32

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...
Post #256724
Posted Wednesday, May 10, 2006 5:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #278875
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse