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

Works for me in SQL2000.

Actually it didnt like PERSISTED. If I comment that line, it works for me.

Function and table are created.

David

On Sun, Mar 15, 2009 at 9:32 PM, Arthur Fuller <fuller.artful at gmail.com>wrote:

> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>