--13--1
EXEC sp_server_info --返回服务器信息
EXEC sp_databases	--返回服务器数据库信息
EXEC sp_who			--返回当前登录用户信息和进程信息
EXEC sp_tables		--返回表信息
EXEC sp_helpdb		--返回特定数据库信息
---13-2
USE BookShopDb
GO
--判断存储过程select_books是否存在
IF OBJECT_ID ( 'SELECT_books', 'P' ) IS NOT NULL 
    DROP PROCEDURE SELECT_books;
GO
--创建存储过程
CREATE PROCEDURE SELECT_books
AS
  SELECT Id,Title,Author,PublishDate,
   UnitPrice,ISBN FROM books
GO
--执行存储过程
EXECUTE SELECT_books 
GO

--13-3
USE BookShopDb
GO
IF OBJECT_ID ( 'SELECT_WITH_pub', 'P' ) IS NOT NULL 
    DROP PROCEDURE SELECT_WITH_pub;
GO
CREATE PROCEDURE SELECT_WITH_pub
@pubname VARCHAR(20)
AS
	SELECT title,author,publishdate
	isbn,name AS publishername
	FROM books a inner join publishers b
	ON a.publisherid=b.id AND
    b.name=@pubname
GO
--执行存储过程
EXEC SELECT_WITH_pub '人民邮电出版社'
GO
 
--13-4
USE BookShopDb
GO

IF OBJECT_ID ( 'SELECT_book_parm', 'P' ) IS NOT NULL 
    DROP PROCEDURE SELECT_book_parm;
GO
CREATE PROCEDURE SELECT_book_parm
@cate VARCHAR(20),
@pubname VARCHAR(20)
AS 
 SELECT  BookName,author,isbn,publishdate,
 Price,b.CategoryName AS categoryname,
 c.PublisherName AS publishername
 FROM 
 bookinfo a, category b,publisher c
 WHERE a.categoryid=b.CategoryID
 AND a.publisherid=c.PublisherID
 AND b.CategoryName LIKE '%'+@cate+'%'
 AND c.PublisherName LIKE '%'+@pubname+'%'
GO
EXEC  SELECT_book_parm 'C' ,'北京'
GO
---13-5
USE BookShopDb
GO
--判断存储过程select_pubdate是否存在
IF OBJECT_ID ( 'SELECT_pubdate', 'P' ) IS NOT NULL 
    DROP PROCEDURE SELECT_pubdate;
GO
CREATE PROCEDURE SELECT_pubdate
@price DECIMAL=100,
@pubdate DATETIME=NULL
AS
    IF(@pubdate IS NULL)
    SET @pubdate=GETDATE()
	SELECT Id,Title,Author,PublishDate,
    UnitPrice,ISBN FROM books
    WHERE unitprice<@price AND
	publishdate<=@pubdate
GO
--测试使用默认参数，即:@price=100,@pubdate=getdate
EXEC SELECT_pubdate 
GO

--13-6
CREATE PROC SUM_sales
@bookname VARCHAR(20) OUTPUT,
@money DECIMAL OUTPUT
AS 
--统计书籍销售信息
SELECT a.BookName 书名,isnull(SUM(b.quantity),0) 销售数量,
ISNULL(SUM(b.quantity*b.price*b.discount),0)销售总价
FROM BookInfo a left join SalesDetails b
ON a.BookID=b.BookID
GROUP BY a.BookName
ORDER BY SUM(b.Quantity) DESC

--返回销售金额最低的书籍
SELECT TOP 1  @bookname=a.bookname,@money=SUM(b.Quantity*b.Price*b.Discount)
FROM BookInfo a inner join SalesDetails b
ON a.BookID=b.BookID
GROUP BY a.BookName
ORDER BY SUM(b.Quantity*b.Price*b.Discount)

--调用存储过程：
DECLARE @name VARCHAR(20)
DECLARE @money DECIMAL
EXECUTE SUM_sales @name OUTPUT,@money OUTPUT
PRINT '销售金额最少的书:'+@name +',金额为：'+CAST(@money AS VARCHAR(4))

---13-9
CREATE PROC SELECT_err
@publisher VARCHAR(50),
@pubdate DATETIME,
@count INT OUTPUT,
@bookName VARCHAR(50) OUTPUT
AS
  DECLARE @pubId INT --保存出版社Id
  IF(@pubdate<=GETDATE())
  BEGIN
  SELECT @pubId=PublisherID
  FROM Publisher WHERE PublisherName=@publisher
  
  --查询出版社出版的书籍信息
  SELECT a.BookName 书名,a.PublishDate 出版日期, a.Price 价格  
  FROM BookInfo a WHERE a.PublisherID=@pubId  
  AND a.PublishDate>@pubdate
  
  --返回销售最号的书籍名称和销售数量
  SELECT TOP 1 @BookName=a.BookName, @count=SUM(b.Quantity)
  FROM BookInfo a,SalesDetails b
  WHERE a.BookID=b.BookID
  AND a.PublisherID=@pubId 
  AND a.PublishDate>@pubdate
  GROUP BY a.BookName
  ORDER BY SUM(b.Quantity) DESC
  END
  ELSE
  BEGIN
       RAISERROR('出版日期不能大于系统日期',16,1)
       return --返回
  END

