USE tempdb
GO

CREATE TABLE salespeople(
  id INT IDENTITY PRIMARY KEY,
  first_name VARCHAR(20),
  last_name  VARCHAR(20),
  territory  VARCHAR(10),
  sales_ytd  DECIMAL(9,2) DEFAULT 0,
  sales_ytd_previous DECIMAL(9,2) DEFAULT 0)
GO

INSERT INTO salespeople VALUES('Bill', 'Williams', 'East', 10000, 80000)
INSERT INTO salespeople VALUES('Mike', 'Williams', 'West', 166000, 800000)
INSERT INTO salespeople VALUES('Fred', 'Williams', 'West', 70000, 33000)
INSERT INTO salespeople VALUES('Peg', 'Bennett', 'East', 200000, 124500)
INSERT INTO salespeople VALUES('Erica', 'Oransky', 'East', 66000, 50000)
INSERT INTO salespeople VALUES('Jay', 'Smith', 'West', 200000, 33000)
GO

CREATE PROCEDURE add_salesperson (
  @first_name VARCHAR(20),
  @last_name  VARCHAR(20),
  @territory  VARCHAR(10),
  @sales_ytd  DECIMAL(9,2) = 0,
  @sales_ytd_previous DECIMAL(9,2) = 0
)
AS
DECLARE @t AS TABLE (
  id VARCHAR(11), sales_ytd DECIMAL(9,2))
-- INSERT the row
INSERT salespeople 
OUTPUT inserted.id, inserted.sales_ytd INTO @t
VALUES(
 @first_name, @last_name, @territory, @sales_ytd, @sales_ytd_previous)
-- send back the table
SELECT * FROM @t
GO

-- test it
EXECUTE add_salesperson 'Phil', 'Fanning', 'West', 999, 0
EXECUTE add_salesperson 'Joe', 'Sales', 'East', DEFAULT, 0
GO

DROP PROCEDURE add_salesperson2
GO
CREATE PROCEDURE add_salesperson2 (
  @first_name VARCHAR(20),
  @last_name  VARCHAR(20),
  @territory  VARCHAR(10),
  @sales_ytd  DECIMAL(9,2) = 0,
  @sales_ytd_previous DECIMAL(9,2) = 0
)
AS
-- INSERT the row
INSERT salespeople 
OUTPUT inserted.id , inserted.sales_ytd
VALUES(
 @first_name, @last_name, @territory, @sales_ytd, @sales_ytd_previous)
GO

-- test it
EXECUTE add_salesperson2 'Phil', 'Fanning', 'West', 999, 0
EXECUTE add_salesperson2 'Joe', 'Sales', 'East', DEFAULT, 0
GO

DROP TABLE current_year_sales
GO
CREATE TABLE current_year_sales (
  sales_id INT IDENTITY PRIMARY KEY, 
  salespeople_id INT,  -- REFERENCES salespeople (id), this isn't allowed
  salesamt DECIMAL(9,2)
)
GO

DROP PROCEDURE add_salesperson3
GO
CREATE PROCEDURE add_salesperson3 (
  @first_name VARCHAR(20),
  @last_name  VARCHAR(20),
  @territory  VARCHAR(10),
  @sales_ytd  DECIMAL(9,2) = 0,
  @sales_ytd_previous DECIMAL(9,2) = 0
)
AS
-- INSERT the row
INSERT salespeople 
OUTPUT inserted.id , inserted.sales_ytd INTO current_year_sales
VALUES(
 @first_name, @last_name, @territory, @sales_ytd, @sales_ytd_previous)
GO

-- test it
EXECUTE add_salesperson3 'Phil', 'Fanning', 'West', 999, 0
EXECUTE add_salesperson3 'Joe', 'Sales', 'East', DEFAULT, 0
GO

CREATE PROCEDURE update_sales_return(
 @personid INT,
 @salesamt DECIMAL(9,2)
)
as
DECLARE @t AS TABLE (id INT, old_sales_ytd DECIMAL(9,2), new_sales_ytd DECIMAL(9,2))
        INSERT INTO current_year_sales VALUES(@personid, @salesamt)
		UPDATE salespeople
          SET sales_ytd = sales_ytd + @salesamt
          OUTPUT inserted.id, deleted.sales_ytd, inserted.sales_ytd INTO @t
          WHERE id = @personid
SELECT * FROM @t
GO

-- test it
EXECUTE update_sales_return 1, 2000
EXECUTE update_sales_return 2, 10000
GO
