1.һݼʾ
CREATE PROCEDURE Read_Title_Record 
@title_id tid, 
@title varchar (80) OUTPUT, 
@pub_id char (4) OUTPUT, 
@ytd_sales int OUTPUT 
AS 
BEGIN 
SELECT @title = title, @pub_id = pub_id, @ytd_sales = ytd_sales 
  FROM titles WHERE title_id = @title_id 
END 
GO  


2.(洢)
CREATE PROCEDURE Write_Title_Record
	
  @title_id tid , 
  @title char (80),
  @type char (12), 
  @pub_id char (4) , 
  @price money, 
  @advance money, 
  @royalty int, 
  @ytd_sales int, 
  @notes varchar (200), 
  @pubdate datetime

AS 
 BEGIN 
  IF EXISTS (SELECT * FROM titles WHERE title_id = @title_id)
    BEGIN 
      UPDATE titles SET title = @title, type = @type, pub_id = @pub_id, price = @price, 
	advance = @advance, royalty = @royalty, ytd_sales = @ytd_sales, notes = @notes, pubdate = @pubdate WHERE title_id = @title_id 
    END 
   ELSE 
     BEGIN
       INSERT titles  (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate )
		 VALUES (@title_id, @title, @type,@pub_id,@price, @advance,@royalty, @ytd_sales,@notes,@pubdate) 
     END 
END

3.һûʱҪɾעϵsalesroyschedеһЩ¼(ع)
CREATE PROCEDURE Retire_Title
@title_id tid
AS 
  BEGIN   
   BEGIN TRANSACTION   
   DELETE FROM sales WHERE title_id = @title_id
   IF @@ERROR <> 0 
     BEGIN        
        ROLLBACK TRANSACTION
        RAISERROR 50002 "Delete of sales record failed"
        RETURN-1    
     END
    DELETE FROM roysched WHERE title_id = @title_id
   
   IF @@ERROR <> 0 
    BEGIN
       ROLLBACK TRANSACTION       
       RAISERROR 50003 "Delete of roysched record failed"
       RETURN-1
    END   
    COMMIT TRANSACTION
    RETURN 0
END
END(Ϊʲô)

4.һд־ļʵù ()
use pubs
GO

CREATE PROCEDURE Write_Log@log_file varchar (255), 
@msg varchar (255) 

AS
  BEGIN
     DECLARE @cmd_str varchar (512)
     DECLARE @date_str varchar (30) 
     SELECT @date_str = CONVERT (varchar (24), GetDate ()) 
     SELECT @cmd_str = 'ECHO ' + @date_str + '--' + @msg 
     SELECT @cmd_str = @cmd_str + '>>' + @log_file 
     EXEC master..xp_cmdshell @cmd_str   
END 

5.һ¼ʾ
use pubs

GO

CREATE PROCEDURE Process_Retired_Titles
AS  
BEGIN
      DECLARE @title_id tid,
              @result int, 
              @msg varchar(255), 
              @log_file varchar(255) 
      SELECT @log_file = 'c:\logs\proc_ret_tit.log' 
	
      DECLARE titles_cursor CURSOR FOR SELECT title_id FROM retired_titles 
      OPEN titles_cursor 
      FETCH NEXT FROM titles_cursor INTO @title_id 
      
      WHILE @@FETCH_STATUS = 0 
           BEGIN 
             EXEC @result = Archive_Old_Title @title_id
         	IF @result < 0
                 BEGIN  
                  SELECT @msg = 'Failed to archive: ' + @title_id 
                   EXEC Write_Log @log_file, @msg 
                 END
             EXEC @result = Retire_Title @title_id 
                IF @result < 0
                  BEGIN 
                    SELECT @msg = 'Failed to delete: ' + @title_id 
                    EXEC Write_Log @log_file, @msg 
                  END 
             FETCH NEXT FROM titles_cursor INTO @title_id 
END
            DEALLOCATE titles_cursor
END

6.һҵ߼ʾ
ӦðбʶһŶͼֵ@stor_id@ord_num@discounttypeԼ@total_sale 
USE pubs
GO

CREATE PROCEDURE Compute_Price 
	@stor_id char(4),
	@ord_num varchar(20),
	@discounttype varchar(40),
	@total_sale money OUTPUT
AS
BEGIN
	DECLARE @qty smallint,
		@title_id tid,
		@list_price money,
		@discount decimal (4,2),
		@curr_sale money,
		@curr_discount money
	SELECT @total_sale = 0
	DECLARE order_cursor CURSOR
	  FOR SELECT title_id, qty FROM sales WHERE stor_id = @stor_id AND ord_num = @ord_num
	OPEN order_cursor
	FETCH NEXT FROM order_cursor INTO @title_id, @qty
	WHILE @@FETCH_STATUS = 0
	    BEGIN
		SELECT @list_price = price FROM titles WHERE title_id = @title_id
		--First attempt to get discount for this specific store based on
		--The quantity ordered
		SELECT @discount = discount FROM discount s WHERE stor_id = @stor_id AND discounttype = discounttype 
		   AND @qty BETWEEN lowqty AND highqty 
			--If no discount exists for this store based on quantity, see if 
			--There is any discount at all for this store 
		IF @discount IS NULL 
		   BEGIN 
			SELECT @discount = discount FROM discount s WHERE stor_id = @stor_id 
				AND discounttype = @discounttype 
		   END 
			--If there is no discount specific to this store, check fora.company 
			--Wide generic discount based on quantity 
		IF @discount IS NULL 
                                  BEGIN 
			SELECT @discount = discount FROM discount s WHERE stor_id IS NULL 
				AND discounttype = @discounttype AND @qty BETWEEN lowqty AND highqty 
		   END 
			--If there is no generic discount based on quantity, the final. chance 
			--For a discount depends on the discount type 
		IF @discount IS NULL 
                                  BEGIN 
			SELECT @discount = discount FROM discount s WHERE stor_id IS NULL AND discounttype = @discounttype 
		   END 
		SELECT @discount = ISNULL (@discount, 0.0000) 
		SELECT @curr_discount = (@qty * @list_price * (@discount/100)) 
		SELECT @curr_sale = @qty * @list_price-@curr_discount 
		SELECT @total_sale = @total_sale + @curr_sale 
		FETCH NEXT FROM order_cursor INTO @title_id, @qty 
	     END 
	CLOSE order_cursor 
	DEALLOCATE order_cursor 
END 

