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