4.21日学习

---存储过程
USE pubs
GO
CREATE PROCEDURE author_information
AS
SELECT au_lname,au_fname,title,pub_name
FROM authors a JOIN titleauthor ta ON a.au_id=ta.au_id JOIN titles t ON t.title_id=ta.title_id JOIN publishers p
ON t.pub_id=p.pub_id
GO

---查看存储过程
USE pubs
GO
sp_helptext author_information
GO

---存储过程一般信息
USE pubs
GO
sp_help author_information
GO

---修改存储过程名字
USE pubs
GO
sp_rename author_information,authors_information
GO

---查看哪个表被存储过程引用的情况
USE pubs
GO
sp_depends authors_information
GO

---执行存储过程
---存储过程调用是批处理第一句，则直接用存储过程名字调用
USE pubs
GO
authors_information
GO

---存储过程调用是批处理不是第一句，则使用关键字EXECUTE或EXEC
USE pubs
EXEC authors_information
GO

--删除存储过程
USE pubs
GO
DROP PROCEDURE authors_information
GO

---通过存储过程传递参数
USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name='titles_sum' AND type='p')
DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @TITLE varchar(40)='%',@SUM money OUTPUT AS
SELECT 'Title Name'=title
FROM titles
WHERE title LIKE @TITLE

SELECT @SUM=SUM(price)
FROM titles
WHERE title LIKE @TITLE
GO

DECLARE @TOTALCOST money
EXECUTE titles_sum 'THE%',@TOTALCOST OUTPUT
IF @TOTALCOST<200
BEGIN
PRINT ''
PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
  SELECT 'The total cost of these titles is $' + RTRIM(CAST(@TOTALCOST AS varchar(20)))