There are three(3) steps to making and using stored procedures:
1)Write the SQL commands in a script file.
2)Execute the CREATE PROCEDURE command to save the procedure in the database.
3)Run the stored procedure to test it.
Create a single SQL script that will do the follow. Comment each step.
Using the Script for provided for this week's video do the following.
The ABC Company now wants you to create stored procedures to calculate federal, state and local taxes for an employee based on their gross income and what state they live in.
Federal taxes will be calculated as follows:
Gross weekly pay < 961.54 the tax rate will be .07
Gross weekly pay between 961.54 and 1923.08 the tax rate will be .08
Gross weekly pay > 1923.08 the tax rate will be .09
State and Local taxes will be calculated using the state and local rates in TTaxRates for each employee.
1)Create the stored procedure uspCalculateTaxes that will accept employee ID and weekly salary. Outputs will be federal, state and local tax. Create a cursor within this stored procedure GetTaxRates which will pull state and local rate from TTaxRates for the employee. For federal tax use an IF statement to set the rate based on the weekly gross and then calculate the tax owed. Call this stored proc from within uspGetGrossPay.
2)Create a Table called TPayrolls. This table should hold employee ID, gross pay (weekly), federal tax, state tax, local tax and the current date (Use GetDate()) for the value on this). Use the IDENTITY property for the PK key column in TPayrolls.
3)Create the stored procedure uspAddPayroll. Once you call uspGetGrossPay which calls uspCalculateTaxes you will then call uspAddPayroll within uspGetGrossPay and insert the calculated data into TPayrolls.
4)Create a test call to the stored procedure after you create it to make sure it works correctly for each type of employee. This means you will need to call 3 salary and 3 hourly employees. You will need 1 each for the federal tax rates of .07, .08 and .09 as listed above for each. Set your data up accordingly. Once you have tested your code please comment your test code out prior to submitting.
Make sure your script runs without any errors from top to bottom.