sp_demo.sql

use master;
drop database if exists demo_customers;
create database demo_customers;
go

use demo_customers;



create function hello (@name varchar(20) = 'everyone') returns varchar(30)
as 
	begin
		return 'hello ' + @name + '!'
	end
go

declare @greeting varchar(30)

exec hello 'SQL class'
exec hello @name = 'Seattle'
exec @greeting = hello 'Seattle'

select @greeting 
-- NAMED COLLECTION OF SQL STATEMENTS

create table Customers (
    CustomerID int identity primary key,
    CustomerFirstName varchar(30),
    CustomerLastName varchar(30),
    CustomerEmail varchar(40)
);
go

create proc pAddCustomer (@firstName varchar(30), @lastName varchar(30) = '', @email varchar(30))
as
    begin 
        -- check if email exists email
        if exists(select * from Customers where CustomerEmail = @email)
            begin
                -- return 404 -- not found
                set @email = ''
            end

        begin try
                begin tran
                    insert into Customers (CustomerFirstName, CustomerLastName, CustomerEmail)
                    values (@firstName, @lastName, @email)
                commit tran
        end try
        begin catch
            rollback transaction
            print 'uh oh, spaghettio'
            print error_number()
            print error_message()
            return 500 -- internal error
        end catch

        -- print some messages and exit
        print 'procedure all done'
        return 200 -- okay
    end
go


exec pAddCustomer 'Jon', 'Jolley', '[email protected]';go

select * from Customers

exec pAddCustomer 'Jane', 'Trello', '[email protected]';


select * from Customers


exec pAddCustomer 'Bo', 'Jackson', '[email protected]';


select * from Customers

exec pAddCustomer 'Jack', 'Jolley', '[email protected]';


select * from Customers;

declare @status_code int
exec @status_code = pAddCustomer 'Bryan', 'Davis', '[email protected]';


select 
	case @status_code
		when 200 then 'OK'
		when 500 then 'Internal Server Error'
		else 'Who Knows'
		end as StatusDescription
;



go





create view vCustomers as
select * from Customers
;
go

deny select, insert, update, delete on Customers to public;
grant select on vCustomers to public;
go

create proc pSelectCustomers (@CustomerID int = 0)
as
    begin
        select *
        from vCustomers
        where CustomerID = @CustomerID
    end
go


grant exec on pSelectCustomers to public;
grant exec on pAddCustomer to public;

go


exec pSelectCustomers 2;
exec pSelectCustomers @CustomerID = 3;
exec pSelectCustomers 10;

-- declare @customer table
-- exec @customer = pSelectCustomers 4;
-- select @customer as CustomerProfile;



exec sp_help 'pSelectCustomers';
exec sp_helptext 'pSelectCustomers';






-- scoped variables
-- you can default values
-- keyword arguments allow mixed positional parameters
-- you can reference DEFAULT parameter
-- return values are implicitly define as an integer; think of status code
-- you can define





-- some concepts
-- - encapsulation
-- - abstraction
-- - recursion




-- best practice
-- - use views and stored procedure
-- - force