SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table variables with calculated columns in UDFs bug?


Table variables with calculated columns in UDFs bug?

Author
Message
David le Quesne
David le Quesne
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1033 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...
David le Quesne
David le Quesne
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1033 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...
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4693 Visits: 715
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search