Help‎ > ‎

SQL Server Alias

posted Mar 23, 2013, 7:41 AM by Surendra Dhanpaul   [ updated Oct 31, 2015, 8:14 AM ]

SQl Server - Referencing alias in calculated columns of stored procedures


Simply put... you can't. Luckily, there are a few options to make you life more simple.

1. Use CTE.
2. Use a temp table... pretty much like the CTE but less complex. We are going to look at using the temp table

Here is your problem. You have to run a report to show the following information.

Employee name         Basic Earnings       Overtime       Gross earnings       Insurance deductions       PAYE deductions         Net Pay

Now, insurance deduction is set such that  if the employee is over 60, no deduction is made. Hence, you need two additional fields:
Date of birth      Age    (not shown on the report)

Ask any SQL Expert and they will tell you this ... that your stored procedure must have a repeat of each calculation.... hence a very long Stored procedure.
Gross earnings = Basic earnings + overtime
paye deductions = (basic earnings + overtime) % rate
insurance ded = case when datedif('y', date of birth, payperiodending) > 60 then 0 else (basic earnings + overtime) * rate end 
finally.... net pay = Basic earnings + overtime - (basic earnings + overtime) % rate - case when datedif('y', date of birth, payperiodending) > 60 then 0 else (basic earnings + overtime) * rate end 

All in the same stored procedure... gee, thats annoying... if the rate changes for paye then you need to adjust the rate of paye everywhere it appears. Suppose you have an even longer and more complex set of calculations as I usually have... what do you do?

The theory is simple. Have a temp table with fields called paye, nis, age, etc
run update sp on each field and you will be able to reference each field as if you were referencing an alias in your stored procedure.