-- John Aromando -- SQL Stored Procedures and Parameters --

use ogcPubLev3
Go

CREATE Procedure spGetCusts
AS                          
SELECT * FROM Customers

--call stored procedure
EXEC spGetCusts
--or
spGetCusts

--view structure
sp_help spgetcusts

--delete spgetcusts
DROP Proc spGetCusts

create proc spGetCustsParam @state nvarchar(2) --data type property required
AS
SELECT * FROM Customers 
where state = @state

--call with parameter
EXEC spGetCustsParam @state = 'ny'

--verify structure
sp_help spgetCustsParam

--edit spGetCustsParam / "teach" parameter to recognize wildcard
alter proc spGetCustsParam @state nvarchar(2)
AS
SELECT * 
FROM Customers
WHERE state like @state + '%' --concatenate wildcard to parameter

--call with parameter
EXEC spGetCustsParam @state = 'ny'

--alternate call
spGetCustsParam 'tx'

--run a parameter on a view through a stored procedure.
--parameters are not allowed on views but this is a clean way around that issue.
Create View AllTablesBase
as
SELECT 
	c.*, 
	s.ordnum, sldate, qty, 
	t.*,
	r.lname, fname, commrate,
	qty*slprice as OrderTotal 
FROM Customers as c 
		JOIN Sales as s ON s.custnum = c.custnum 
		JOIN Titles as t ON t.partnum = s.partnum 
		JOIN Slspers as r ON r.repid = c.repid	


create proc spThruViewParam @state nvarchar(2)
AS
SELECT * FROM AllTablesBase 
where state like @state + '%'

--call with parameter
EXEC spThruViewParam @state = 'N%'

--alternate call
spThruViewParam 'tx'