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  :    https://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

This page was last updated on May 01, 2006 04:28 PM.