/*
第七章习题
*/
/*1*/
drop procedure p1
go
create procedure p1 @cname varchar(50)
as
;with tmp as(
select b.categoryid,categoryname,SUM(amount) as 'amt' from OrderItems a
join Products b on a.ProductID =b.ProductID
join Categories c on b.CategoryID =c.CategoryID
group by b.CategoryID ,CategoryName )
select amt from tmp where categoryname=@cname
go
execute p1 'Confections'
/*2*/
drop procedure p2
go
create procedure p2 @pname varchar(80)
as
;with tmp as (
select
a.productid,productname,RANK()
over
(order
by
sum(amount))
as
'rankid'
from
OrderItems a
join Products b on a.ProductID =b.ProductID
group by a.ProductID ,productname)
select rankid from tmp where ProductName=@pname
go
execute p2 'Tofu'
/*3*/
drop procedure p3
go
create procedure p3 @tablename varchar(40),@cname varchar(40)
as
declare @sql varchar(2000)
set
@sql='if
not
exists
(select
data_type,character_maximum_length
from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='''
set @sql=@sql+@tablename+''' and COLUMN_NAME='''
set @sql=@sql+@cname+''')'+CHAR(13)
set @sql=@sql+char(9)+'print '''+'*'+''''+CHAR(13)
set @sql=@sql+'else'+CHAR(13)
set
@sql=@sql+char(9)+'select
data_type,character_maximum_length
from