gistfile1.txt

-- ================================================
-- 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 'ct'


*/
 --=============================================
 --Author:  	Maung San
 --Create date: 04/15/2013
 --Description:	this is a test stored procedure
 --=============================================
create PROCEDURE uspGetPubStatsADA 
		@publishcode varchar(20)
		
AS
declare @var1 as int
declare @var2 as bit
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)

		)

BEGIN
 
-- Get the published code from supplied code
 
insert into @MyTableVariable
 
Select publishername 
from publisher
Where publishercode = @publishcode;
 
 
       
--RAISE NOTICE 'Publisher Name: (%) ', pubname ;

 insert into @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 ;
 
Select count(bookcode) INTO TotalbookCount
from book
Where book.publishercode = @publishcode;
 
print  'Number of different books published:  (%) '+ @TotalbookCount;
 
Select title INTO Booktitle 
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;
 
Create table temp AS

Select title, sum(onHand) as BigSum
From book,inventory
Where book.bookcode = inventory.bookcode AND
book.publishercode = @publishcode
Group by book.title;
 
Select title INTO @Booktitle
From temp
Where BigSum = (Select max(BigSum)
               From temp);
 
print 'The book title with the most copies from the publisher is: (%)  '+ @Booktitle;
 
               
Select max(Bigsum) INTO @onHandValues
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;
 
 
Select sum(SumofHand) INTO @SumofonHand
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 ;
 
 
DROP TABLE temp;
RETURN (0);
END;