[dba-SQLServer] Why doesn't this work?

Arthur Fuller fuller.artful at gmail.com
Sun Mar 15 23:32:51 CDT 2009


I've been staring at this too long and I'm bleary now, so it's time for a
fresh pair of eyes...

<sql>
 USE [AdventureWorks]
GO
-- Create UDF to use in computed column expression
CREATE FUNCTION [dbo].[UDF_CalculatePay]
    (
      @basicPay INT,
      @BonusPercentage TINYINT,
      @TaxPercentage TINYINT
    )
RETURNS INT
    WITH SCHEMABINDING
AS BEGIN
    DECLARE @TotalPay INT
    SET @TotalPay = @basicPay + @basicPay * @bonusPercentage / 100 -
@basicPay
        * @taxPercentage / 100
    RETURN @TotalPay
   END
GO

IF OBJECT_ID('CCIndexTest', 'U') IS NOT NULL
    DROP TABLE CCIndexTest
GO
-- Create table CCIndexTest with two computed columns
CREATE TABLE [dbo].[CCIndexTest]
    (
      [EmpNumb] [INT] NOT NULL,
      [DOBirth] [DATETIME] NULL,
      [DORetirement] AS ( DATEADD(YEAR, ( 60 ), [DOBirth]) - ( 1 ) )
PERSISTED,
      [BasicPay] [SMALLINT] NULL,
      [BonusPercentage] [TINYINT] NULL,
      [TaxPercentage] [TINYINT] NULL,
      [TotalPay] AS [dbo].[UDF_CalculatePay](basicPay, BonusPercentage,
                                             TaxPercentage)
    )
ON  [PRIMARY]
GO
</sql>

I get arrested on line 10 (of the Create Table part -- the udf compiles
fine) with "Invalid Column Name basicPay". I do not understand what is wrong
with this. Do you?

TIA,
Arthur



More information about the dba-SQLServer mailing list