存储过程

use  pubs
CREATE   PROCEDURE  p_au_info_all
AS
SELECT  au_lname, au_fname, title, pub_name
    FROM  authors a  FULL   JOIN  titleauthor ta
       ON  a.au_id  =  ta.au_id  FULL   JOIN  titles t
       ON  t.title_id  =  ta.title_id  FULL   JOIN  publishers p
       ON  t.pub_id  =  p.pub_id
GO

EXEC  p_au_info_all


SELECT   *   FROM  authors
SELECT   *   FROM  titleauthor
SELECT   *   FROM  titles
SELECT   *   FROM  publishers


use  master
xp_cmdshell  ' dir C: '




DROP   PROCEDURE  p_au_info
CREATE   PROCEDURE  p_au_info
     @lastname   nvarchar ( 40 ),
     @firstname   nvarchar ( 20 )
AS
SELECT  au_lname,au_fname,title,pub_name
     FROM  authors a  INNER   JOIN  titleauthor ta 
         ON  a.au_id = ta.au_id  INNER   JOIN  titles t
         ON  ta.title_id = t.title_id  INNER   JOIN  publishers p
         ON  t.pub_id = p.pub_id
     WHERE  au_lname = @lastname   AND  au_fname = @firstname


EXECUTE  p_au_info  ' Dull ' , ' Ann '




USE  pubs
IF   EXISTS  ( SELECT  name  FROM  sysobjects 
       WHERE  name  =   ' au_info2 '   AND  type  =   ' P ' )
    DROP   PROCEDURE  au_info2
GO
USE  pubs
GO
CREATE   PROCEDURE  au_info2
    @lastname   varchar ( 30 )  =   ' D% ' ,
    @firstname   varchar ( 18 )  =   ' % '
AS  
SELECT  au_lname, au_fname, title, pub_name
FROM  authors a  INNER   JOIN  titleauthor ta
    ON  a.au_id  =  ta.au_id  INNER   JOIN  titles t
    ON  t.title_id  =  ta.title_id  INNER   JOIN  publishers p
    ON  t.pub_id  =  p.pub_id
WHERE  au_fname  LIKE   @firstname
    AND  au_lname  LIKE   @lastname
GO

EXECUTE  au_info2  @firstname = ' A% '

DROP   PROC  p_au_info3
SELECT   *   FROM  authors

CREATE   PROCEDURE  p_au_info3  @@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

DECLARE   @@TOTALCOST   money

EXECUTE  p_au_info3  @@TITLE = ' THE% ' , @@SUM = @@TOTALCOST  OUTPUT


IF   @@TOTALCOST   <   10
BEGIN
    PRINT   '   '
    PRINT   ' All of these titles can be purchased for less than $200. '
END
ELSE
    SELECT   ' The total cost of these titles is $ '  
          +   CAST ( @@TOTALCOST   AS   varchar ( 20 ))

DECLARE  authors_cursor  CURSOR
    FOR   SELECT   *   FROM  authors
OPEN  authors_cursor
FETCH   NEXT   FROM  authors_cursor
CLOSE  authors_cursor

USE  pubs
IF   EXISTS  ( SELECT  name  FROM  sysobjects 
       WHERE  name  =   ' titles_cursor '   and  type  =   ' P ' )
DROP   PROCEDURE  titles_cursor
GO
CREATE   PROCEDURE  titles_cursor  @titles_cursor   CURSOR   VARYING  OUTPUT
AS
SET   @titles_cursor   =   CURSOR
FORWARD_ONLY STATIC  FOR
SELECT   *
FROM  titles

OPEN   @titles_cursor
GO

DECLARE   @MyCursor   CURSOR
EXEC  titles_cursor  @titles_cursor   =   @MyCursor  OUTPUT
WHILE  ( @@FETCH_STATUS   =   0 )
BEGIN
    FETCH   NEXT   FROM   @MyCursor
END
CLOSE   @MyCursor
DEALLOCATE   @MyCursor
GO

DECLARE   @i   int
DECLARE   @MyCursor   CURSOR
EXEC  titles_cursor  @titles_cursor   =   @MyCursor  OUTPUT
SET   @i = 0
WHILE  ( @i = 5 )
BEGIN
    FETCH   NEXT   FROM   @MyCursor
END
SET   @i = @i + 1
CLOSE   @MyCursor
DEALLOCATE   @MyCursor
GO

USE  pubs
GO
DECLARE   @MyCursor   CURSOR
EXEC  titles_cursor  @titles_cursor   =   @MyCursor  OUTPUT
WHILE  ( @@FETCH_STATUS   =   0 )
BEGIN
    FETCH   NEXT   FROM   @MyCursor
END
CLOSE   @MyCursor
DEALLOCATE   @MyCursor
GO

use  pubs
CREATE   PROCEDURE  p_au_info_all
AS
SELECT  au_lname, au_fname, title, pub_name
    FROM  authors a  FULL   JOIN  titleauthor ta
       ON  a.au_id  =  ta.au_id  FULL   JOIN  titles t
       ON  t.title_id  =  ta.title_id  FULL   JOIN  publishers p
       ON  t.pub_id  =  p.pub_id
GO
-- --------------------------------------------------
EXEC  p_au_info_all
-- -------------------------------------------------------------------------------------------------

SELECT   *   FROM  authors
SELECT   *   FROM  titleauthor
SELECT   *   FROM  titles
SELECT   *   FROM  publishers


use  master
xp_cmdshell  ' dir C: '

-- ----------------------------------------------------------------------------------------------------

DROP   PROCEDURE  p_au_info
-- -----------------------------------------------------------------------------------------------
CREATE   PROCEDURE  p_au_info
     @lastname   nvarchar ( 40 ),
     @firstname   nvarchar ( 20 )
AS
SELECT  au_lname,au_fname,title,pub_name
     FROM  authors a  INNER   JOIN  titleauthor ta 
         ON  a.au_id = ta.au_id  INNER   JOIN  titles t
         ON  ta.title_id = t.title_id  INNER   JOIN  publishers p
         ON  t.pub_id = p.pub_id
     WHERE  au_lname = @lastname   AND  au_fname = @firstname

-- -------------------------------------------------------------------------------------------------------------------------
EXECUTE  p_au_info  ' Dull ' , ' Ann '

-- ------------------------------------------------------------------------------------------------------------


USE  pubs
IF   EXISTS  ( SELECT  name  FROM  sysobjects 
       WHERE  name  =   ' au_info2 '   AND  type  =   ' P ' )
    DROP   PROCEDURE  au_info2
GO
-- ---------------------
USE  pubs
GO
CREATE   PROCEDURE  au_info2
    @lastname   varchar ( 30 )  =   ' D% ' ,
    @firstname   varchar ( 18 )  =   ' % '
AS  
SELECT  au_lname, au_fname, title, pub_name
FROM  authors a  INNER   JOIN  titleauthor ta
    ON  a.au_id  =  ta.au_id  INNER   JOIN  titles t
    ON  t.title_id  =  ta.title_id  INNER   JOIN  publishers p
    ON  t.pub_id  =  p.pub_id
WHERE  au_fname  LIKE   @firstname
    AND  au_lname  LIKE   @lastname
GO
-- ------------------------------------------------------------------------------------------
EXECUTE  au_info2  @firstname = ' A% '
-- -------------------------------------------------------------------------------------------
DROP   PROC  p_au_info3
SELECT   *   FROM  authors
-- -------------------------------------------------------------------------------------------------------------------------------------------------
CREATE   PROCEDURE  p_au_info3  @@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

DECLARE   @@TOTALCOST   money

EXECUTE  p_au_info3  @@TITLE = ' THE% ' , @@SUM = @@TOTALCOST  OUTPUT


IF   @@TOTALCOST   <   10
BEGIN
    PRINT   '   '
    PRINT   ' All of these titles can be purchased for less than $200. '
END
ELSE
    SELECT   ' The total cost of these titles is $ '  
          +   CAST ( @@TOTALCOST   AS   varchar ( 20 ))

DECLARE  authors_cursor  CURSOR
    FOR   SELECT   *   FROM  authors
OPEN  authors_cursor
FETCH   NEXT   FROM  authors_cursor
CLOSE  authors_cursor

USE  pubs
IF   EXISTS  ( SELECT  name  FROM  sysobjects 
       WHERE  name  =   ' titles_cursor '   and  type  =   ' P ' )
DROP   PROCEDURE  titles_cursor
GO
CREATE   PROCEDURE  titles_cursor  @titles_cursor   CURSOR   VARYING  OUTPUT
AS
SET   @titles_cursor   =   CURSOR
FORWARD_ONLY STATIC  FOR
SELECT   *
FROM  titles

OPEN   @titles_cursor
GO

DECLARE   @MyCursor   CURSOR
EXEC  titles_cursor  @titles_cursor   =   @MyCursor  OUTPUT
WHILE  ( @@FETCH_STATUS   =   0 )
BEGIN
    FETCH   NEXT   FROM   @MyCursor
END
CLOSE   @MyCursor
DEALLOCATE   @MyCursor
GO

DECLARE   @i   int
DECLARE   @MyCursor   CURSOR
EXEC  titles_cursor  @titles_cursor   =   @MyCursor  OUTPUT
SET   @i = 0
WHILE  ( @i = 5 )
BEGIN
    FETCH   NEXT   FROM   @MyCursor
END
SET   @i = @i + 1
CLOSE   @MyCursor
DEALLOCATE   @MyCursor
GO

USE  pubs
GO
DECLARE   @MyCursor   CURSOR
EXEC  titles_cursor  @titles_cursor   =   @MyCursor  OUTPUT
WHILE  ( @@FETCH_STATUS   =   0 )
BEGIN
    FETCH   NEXT   FROM   @MyCursor
END
CLOSE   @MyCursor
DEALLOCATE   @MyCursor
GO