IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLForBooks'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.CreateHTMLForBooks
    IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLForBooks'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.CreateHTMLForBooks >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.CreateHTMLForBooks >>>'
END
go
CREATE PROCEDURE CreateHTMLForBooks (
        @NoToc bit = 1,
        @JustToc bit = 0,
        @CatLike varchar(30) = NULL
)
--WITH ENCRYPTION
AS
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	17 Jan 2000						*/
/*	Description:	This SP is used to create the HTML pages for my books.  */
/*			The parameters can be used to generate the TOC also or a*/
/*			specific category alone. By default, the HTML content is*/
/*			generated for all the categories.			*/
/*			The details for the books are stored in various tables. */
/*			The HTML file is generated based on the categories and  */
/*			the books in each one. The FrontPage template files are */
/*			used with the Web Assistant jobs to generate content.	*/
/*			All the HTML pages are created under a directory on the */
/*			root of the web site.					*/
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
DECLARE @cat smallint, @desc varchar(255), @querystr varchar(8000),
	@mydocdir varchar(75), @myhtmlfile varchar(255),
	@mytplfile varchar(255), @mywebdir varchar(75),
	@mybookdir varchar(30)

SELECT @mywebdir = Value
FROM Settings
WHERE Setting = 'FrontPage Web Directory'

SELECT @mydocdir = REPLACE(REPLACE(Value, '%s', ''), '"', '')
FROM Settings
WHERE Setting = 'My Documents'

SELECT @myhtmlfile = @mydocdir + @mywebdir + Value
FROM Settings
WHERE Setting = 'Books HTML File'

SELECT @mytplfile = @mydocdir + @mywebdir + Value
FROM Settings
WHERE Setting = 'Books TOC Template File'

SELECT 	@CatLike = COALESCE(@CatLike, '%'),
	@querystr ='
SELECT Comments
FROM Book_Categories
WHERE Category = 0
SELECT Description + ''.htm'', Description, Comments
FROM Book_Categories
WHERE Category > 0
ORDER BY SortOrder'

IF @NoToc = 0
	EXECUTE sp_makewebtask @outputfile = @myhtmlfile, @query = @querystr,
			@templatefile = @mytplfile, @dbname = 'My Personal Db'
IF @JustToc = 1
	RETURN

SELECT	@mytplfile = @mydocdir + @mywebdir + Value
FROM Settings
WHERE Setting = 'Books Template File'

SELECT	@mybookdir = Value
FROM Settings
WHERE Setting = 'Books HTML Directory'

DECLARE cats CURSOR FOR
SELECT Description, Category
FROM Book_Categories
WHERE Category > 0 And Description LIKE @CatLike
ORDER BY SortOrder

OPEN cats
WHILE(1=1)
BEGIN
	FETCH cats INTO @desc, @cat
	IF @@FETCH_STATUS < 0 BREAK

	SELECT  @myhtmlfile = @mydocdir + @mywebdir + @mybookdir + @desc + '.htm',
		@querystr ='
SELECT ''' + @desc + '''
SELECT BookOrder, Title, Author, Comments
FROM [my books]
WHERE category = ' + STR(@cat) + 'ORDER BY CatOrder, BookOrder'
	
	EXECUTE sp_makewebtask @outputfile = @myhtmlfile, @query = @querystr,
			@templatefile = @mytplfile, @dbname = 'My Personal Db'
END
DEALLOCATE cats
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLForBooks'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.CreateHTMLForBooks To Public
    PRINT '<<< CREATED PROCEDURE dbo.CreateHTMLForBooks >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.CreateHTMLForBooks >>>'
go