SQL Script to generate payment information for salesmen

Sales_Pay.sql

USE Sandbox

DROP TABLE Salesmen
DROP TABLE Pay
DROP PROC spReadPayRecord
DROP FUNCTION fnCalcPay
DROP FUNCTION fnCalcComm
DROP FUNCTION fnCalcBonus
DROP PROC spUpdatePay

IF OBJECT_ID('Salesmen') IS NULL
BEGIN
CREATE TABLE Salesmen
(
	ID INT IDENTITY PRIMARY KEY,
	ID_Sales INT NOT NULL,
	Name VARCHAR(20) NOT NULL,
	Service_Years INT,
	Base_Pay MONEY,
	Sales MONEY
)
	BULK INSERT Salesmen
	FROM 'C:\Salesmen.txt'
	WITH (  FIELDTERMINATOR = ' ',
			ROWTERMINATOR = '\n')
END

CREATE TABLE Pay
(
	ID INT IDENTITY PRIMARY KEY,
	Commission MONEY, 
	Bonus MONEY, 
	Total MONEY
)

GO
CREATE PROC spReadPayRecord(@ID INT)
AS
BEGIN
	RETURN (SELECT Total FROM Pay WHERE ID = @ID)
END
GO

CREATE FUNCTION fnCalcBonus(@Service_Years INT)
RETURNS MONEY
AS 
BEGIN
	DECLARE @Bonus MONEY

	SET @Bonus = CASE
		WHEN @Service_Years BETWEEN 1 AND 5 THEN 500
		WHEN @Service_Years BETWEEN 6 AND 10 THEN 1000
		WHEN @Service_Years BETWEEN 11 and 15 THEN 2000
		WHEN @Service_Years BETWEEN 16 AND 20 THEN 3000
		WHEN @Service_Years > 20 THEN 5000
		ELSE 0
	END
	RETURN @Bonus
END


GO
CREATE FUNCTION fnCalcComm(@Sales MONEY)
RETURNS MONEY
AS 
BEGIN
	DECLARE @Commission MONEY
	SET @Commission = CASE
		WHEN @Sales BETWEEN 0 AND 100 THEN @Sales * .03
		WHEN @Sales BETWEEN 100 AND 500 THEN @Sales * .035
		WHEN @Sales BETWEEN 500 AND 1000 THEN @Sales * .04
		WHEN @Sales BETWEEN 1000 AND 2000 THEN @Sales * .045
		WHEN @Sales > 2000 THEN @Sales * .05
	END
	RETURN @Commission
END

GO
CREATE FUNCTION fnCalcPay(@Commission MONEY, @Bonus MONEY, @Base_Pay MONEY)
RETURNS MONEY
AS
BEGIN
	RETURN @Commission + @Bonus + @Base_Pay
END

GO
CREATE PROC spUpdatePay(@ID INT)
AS
BEGIN

	DECLARE @Service_ID INT,
			@Bonus MONEY
	SET @Service_ID = (SELECT Service_Years FROM Salesmen WHERE Salesmen.ID = @ID)
	EXEC @Bonus = fnCalcBonus @Service_ID

	DECLARE @Sales MONEY,
			@Commission MONEY
	SET @Sales = (SELECT Sales FROM Salesmen WHERE ID = @ID)
	EXEC @Commission = fnCalcComm @Sales

	DECLARE @Total MONEY,
			@Base_Pay MONEY
	SET @Base_Pay = (SELECT Base_Pay FROM Salesmen WHERE ID = @ID)
	EXEC @Total = fnCalcPay @Commission, @Bonus, @Base_Pay

	IF ((SELECT COUNT(*) FROM Salesmen) < @ID) 
	BEGIN
		UPDATE Pay
		SET Commission = @Commission, Bonus = @Bonus, Total = @Total
		WHERE @ID = ID
	END
	ELSE 
	BEGIN
		INSERT INTO Pay
		VALUES (@Commission, @Bonus, @Total)
	END
END
GO



DECLARE @Index SMALLINT
SET @Index = 1


WHILE (SELECT COUNT(*) FROM Salesmen) > @Index
	BEGIN
		EXEC spUpdatePay @Index
		SET @Index = @Index + 1
	END

SELECT CAST(ID_Sales AS VARCHAR(3)) AS ID,
	CAST(Name AS VARCHAR(10)) AS Name,
	CAST(Service_Years AS VARCHAR(3)) AS [Exp],
	CAST(Base_Pay AS VARCHAR(10)) AS Base,
	CAST(Sales AS VARCHAR(10)) AS Sales,
	CAST(Commission AS VARCHAR(10)) AS Commission,
	CAST(Bonus AS VARCHAR(7)) AS Bonus,
	CAST(Total AS VARCHAR(10)) AS Total
FROM Salesmen JOIN Pay
	ON Salesmen.ID = Pay.ID

GO