Giai BT

--BT1 p204
--Xoa procedure neu no co ton tai truoc
use pubs
go
drop Get_Sales_Avg
go
--Cach 1
create procedure Get_Sales_Avg @a_id id
as
select avg(ytd_sales) as 'Gia tri TB' from titles
where title_id in (select title_id from titleauthor where au_id=@a_id)
go
--Cach 2
create procedure Get_Sales_Avg_1 @a_id varchar (20)
as
select avg(ytd_sales) as 'Gia tri TB' from titles t inner join titleauthor ta
on t.title_id=ta.title_id where au_id=@a_id
go
----------
create procedure Get_Sales_Avg_1 @a_id varchar (20)
as
select avg(ytd_sales) as 'Gia tri TB' from titles t,titleauthor ta
where t.title_id=ta.title_id and au_id=@a_id
go
exec Get_Sales_Avg_1 '267-41-2394'
--BT2: p204
exec Get_Sales_Avg '267-41-2394' with recompile

--BT3: p204
sp_stored_procedures
--BT4: p204
sp_helptext Get_Sales_Avg

--Bai tap ve nha
--Bai 1
create procedure Get_sales_Avg @a_id varchar(11) as
if (select count(*) from authors where au_id=@a_id)=0
begin
raiserror('AuthorID khong nhin thay',1,2)
end
else
select avg(ytd_sales) as 'Gia tri TB' from titles t,titleauthor ta
where t.title_id=ta.title_id and au_id=@a_id

exec Get_sales_Avg '213-46-8916'
--Cau 2:

alter procedure Get_sales_Avg @a_id varchar(11) as
if (select count(*) from authors where au_id=@a_id)!=0
begin
declare @x int
select @x=avg(ytd_sales) from titles t,titleauthor ta
where t.title_id=ta.title_id and au_id=@a_id
if @x > 5000
print 'High year-to-date sales'
else
print 'Low year-to-date sales'
end

exec Get_sales_Avg '267-41-2394'