gistfile1.sql

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
--

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
This is how you execute stored procedure.


execute uspGetPubStatsADA 'AP'


*/
 --=============================================
 --Author:  	Maung San
 --Create date: 04/15/2013
 --Description:	this is a test stored procedure
 --=============================================
create PROCEDURE uspGetPubStatsADA 
		@publishcode varchar(20)
		
AS
declare @pubname text
declare @AuthorCount int
declare @TotalbookCount int
declare @Booktitle text;
declare @onHandValues int;
declare @SumofonHand int;

declare @MyTableVariable Table (
		column1  varchar(20),
		column2  varchar(20),
		column3  varchar(20),
		column4  varchar(20),
		column5  varchar(20)
		)
		


BEGIN
 
-- Get the published code from supplied code
 
set @pubname =(
Select publishername 
from publisher
Where publishercode = @publishcode
 )
 
       
print 'Publisher Name: (%) ' + pubname ;


SET @AuthorCount = (Select count ( DISTINCT author.authornum)
from book,wrote,author
Where book.publishercode = @publishcode AND
book.bookcode = wrote.bookcode AND wrote.authornum = author.authornum);
 
Print  'Number of distinct authors for publisher:  (%) '+ @AuthorCount ;
 

set @TotalbookCount=( Select count(bookcode) 
from book
Where book.publishercode = @publishcode)
 
print  'Number of different books published:  (%) '+ @TotalbookCount;
 

set @Booktitle=( Select title 
from book,wrote,author
Where book.publishercode = @publishcode AND
book.bookcode = wrote.bookcode AND wrote.authornum = author.authornum)
 
print 'Number of distinct authors for publisher:  (%) '+ @AuthorCount;
 



SELECT book.title
     , sum(inventory.onHand) as BigSum
  FROM book
INNER
  JOIN inventory
    ON inventory.bookcode = book.bookcode
 WHERE book.publishercode = @publishcode
GROUP 
    BY book.title
ORDER
    BY BigSum 
 
print 'The book title with the most copies from the publisher is: (%)  '+ @Booktitle;
 
set @onHandValues = (           
Select max(Bigsum) 
From (SELECT sum(onhand) as Bigsum,
  book.title  
FROM 
  book, 
  inventory
WHERE 
  book.bookcode = inventory.bookcode AND
  book.publishercode = @publishcode
Group by book.title) as Bignum 
 )
print 'Number of copies for the above book:  (%)'+ @onHandValues;
 
set @SumofonHand = (
Select sum(SumofHand)
From (SELECT sum(onhand) as SumofHand
  FROM   book,  inventory
  WHERE 
  book.bookcode = inventory.bookcode AND
  book.publishercode = @publishcode)as foo

 )
print 'Cummulative Sum of onHand books for publisher:  (%)'+ SumofonHand ;