how to add computed columns in sql??

Question

How to add two computed columns in sql ??
I am using visual studio 2010 ,where i have created the database by using server explorer.Now i want to add two columns which are already computed.
e.g
i have columns like

so i want deduction should be pf+it+medicalallwnceded where pf is computed column as 0.25* salary
i m getting error :Unable to modify table.
Computed column 'pf' in table 'Tmp_ps' is not allowed to be used in another computed-column definition. where ps is the table name.
also i tried by using function
my function is:
CREATE FUNCTION [dbo].[deduction](@pf Float ,@it Float , @medicalallwnceded Float )
RETURNS FLOAT
AS
BEGIN
DECLARE @deduction Float

SET @deduction = @pf + @it + @medicalallwnceded

RETURN

@deduction

END
where i m getting error like - Unable to modify table.
Must declare the scalar variable "@pf".

Jolly Priya · Answer

I also tried in that way but while filling the formula for computed column specifications property of deduction column like ( [dbo].[deduction]([pf][it] [medicalallwnceded] )) i m getting error as Computed column 'pf' in table 'Tmp_ps' is not allowed to be used in another computed-column definition. as i m using pf : 0.25*salary. Soi tried to put the formula for deduction as: (((0.25)*[salary])+[it]+[medicalallwnceded]) which is working but its getting more complex..plz suggest.

Sunny Sharma · Answer

Hi Priya I've created a sample Table Function and tested it. Works like a charm. Check the script below: ------------------------------------------------------------------------ CREATE TABLE TEST( SALARY FLOAT PF AS (SALARY*.025) IT FLOAT MEDICAL FLOAT ) --------------------------------------------------------------------- CREATE FUNCTION GetDeduction(@pf FLOAT @it FLOAT @medical FLOAT) RETURNS FLOAT AS BEGIN DECLARE @deduction FLOAT; SET @deduction=@pf+@it+@medical; RETURN @deduction; END -------------------------------------------------------------------------- INSERT INTO TEST(SALARY IT MEDICAL) VALUES(20000200300); SELECT * FROM TEST; SELECT * dbo.GetDeduction(PF IT MEDICAL)AS DEDUCTION FROM TEST; -------------------------------------------------------------------------- Give it a go and let me know if it works. mark this as answer if it helps! Thanks.