9-05.sql

sql2000工程实践一书第9张源代码

USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Oakland_authors' AND type = 'P')
   DROP PROCEDURE Oakland_authors
GO
USE pubs
GO
CREATE PROCEDURE Oakland_authors
AS 
SELECT au_fname, au_lname, address, city, zip
FROM pubs..authors
WHERE city = 'Oakland'
and state = 'CA'
ORDER BY au_lname, au_fname
GO
/* 查看该存储过程的源代码*/
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id
WHERE o.type = 'P' and o.name = 'Oakland_authors'
/* 将执行该存储过程的权限授予public 角色*/
GRANT EXECUTE ON Oakland_authors TO public
GO
/* 下面对该存储过程进行修改使其能够显示出所有居住在加里福尼亚的作者而不考虑其它地
区居住的作者*/
ALTER PROCEDURE Oakland_authors
WITH ENCRYPTION
AS 
SELECT au_fname, au_lname, address, city, zip
FROM pubs..authors
WHERE state = 'CA'
ORDER BY au_lname, au_fname
GO
/* 查看该存储过程的源代码*/
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id
WHERE o.type = 'P' and o.name = 'Oakland_authors'
GO