IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLForCDs'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.CreateHTMLForCDs
    IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLForCDs'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.CreateHTMLForCDs >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.CreateHTMLForCDs >>>'
END
go
CREATE PROCEDURE CreateHTMLForCDs (
        @NoToc bit = 1,
        @JustToc bit = 0,
        @ArtistIDsLike varchar(30) = NULL
)
--WITH ENCRYPTION
AS
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	05 Feb 2000						*/
/*	Description:	This SP is used to create the HTML pages for my CDs.    */
/*			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         	*/
/********************************************************************************/
-- Keep temporary tables' DDLs together to avoid recompilation
SELECT [Grouping], 0 AS SortOrder, CAST(NULL AS varchar(255)) AS Names,
	CAST(NULL AS varchar(255)) AS ArtistIDs, COUNT(*) AS Cnt
INTO ##A
FROM Artists
WHERE [Grouping] >= 0 And
	(CHARINDEX('~' + CAST(ArtistID AS varchar) + '~',
		@ArtistIDsLike) > 0 Or @ArtistIDsLike IS NULL)
GROUP BY [Grouping]
CREATE TABLE ##C ( CDOrder int IDENTITY(1, 1) NOT NULL,
			Artist varchar(255) NULL, Title varchar(1000) NULL )

CREATE TABLE #T (TitleID int, Title varchar(1000) NULL)


-- Local variables
DECLARE @NumCnt int, @ArtistIDs varchar(255),
	@Names varchar(255), @Grouping smallint, @Cnt int,
	@querystr varchar(8000), @mywebdir varchar(255),
	@mydocdir varchar(255), @myhtmlfile varchar(255),
	@mytplfile varchar(255), @mycdsdir varchar(255),
	@desc varchar(30), @sortorder smallint

DECLARE Artists CURSOR FOR SELECT [Grouping], Cnt FROM ##A

OPEN Artists
WHILE(1=1)
BEGIN
	FETCH Artists INTO @Grouping, @Cnt
	IF @@FETCH_STATUS < 0 BREAK

	SELECT @Names = '', @ArtistIDs = '~', @NumCnt = 1, @SortOrder = 32767
	UPDATE A
	SET @Names = @Names + CASE WHEN CHARINDEX(',', Artist) > 0
					THEN '"' + RTRIM(Artist) + '"'
					ELSE RTRIM(Artist)
			      END + 
			CASE WHEN @NumCnt < @Cnt THEN ', ' ELSE '' END,
		@ArtistIDs = @ArtistIDs + CAST(ArtistID AS varchar) + '~',
		@NumCnt  = @NumCnt + 1,
		@SortOrder = CASE WHEN SortOrder < @SortOrder
				THEN SortOrder
				ELSE @SortOrder
			     END
	FROM Artists A
	WHERE A.[Grouping] = @Grouping

	UPDATE ##A
	SET Names = @Names, ArtistIDs = @ArtistIDs, SortOrder = @SortOrder
	WHERE CURRENT OF Artists

END
DEALLOCATE Artists

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 = 'CDs HTML File'

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

SELECT	@querystr ='
SELECT Comments
FROM Music_Categories
WHERE Category = 0
SELECT ''Music'' + CAST([Grouping] AS varchar) + ''.htm'', Names
FROM ##A ORDER BY SortOrder'

IF @NoToc = 0
	EXECUTE sp_makewebtask @outputfile = @myhtmlfile, @query = @querystr,
			@templatefile = @mytplfile, @dbname = 'My Personal Db'
IF @JustToc = 1
BEGIN
	DROP TABLE ##A
	DROP TABLE ##C
	RETURN
END

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

SELECT	@mycdsdir = Value
FROM Settings
WHERE Setting = 'CDs HTML Directory'

DECLARE Artists CURSOR FOR
SELECT REPLACE(Names, '"', ''), ArtistIDs, 'Music' + CAST([Grouping] AS varchar)
FROM ##A

OPEN Artists
WHILE(1=1)
BEGIN
	FETCH Artists INTO @Names, @ArtistIDs, @desc
	IF @@FETCH_STATUS < 0 BREAK

	-- Temporary tables are used to manipulate the linked view that gets the titles
	-- from the Windows 2000 DeluxeCD database. Direct queries using equality
	-- operators on the views & other tables resulted in "Access Violation"
	-- messages on the server & it does not work. Hence, this approach of using
	-- temporary tables to store intermediate SELECT results.
	INSERT #T
	SELECT TitleID, CAST(Title AS varchar(1000))
	FROM [DeluxeCD Titles] t
	WHERE EXISTS(SELECT * FROM ##A
			WHERE CHARINDEX(RTRIM(t.Artist) + ',', @Names + ',') > 0)

	INSERT ##C (Artist, Title)
	SELECT A.Artist, t.Title
	FROM #T t JOIN CDs c
	ON t.TitleID = c.TitleID
	JOIN Artists A
	ON A.ArtistID = c.ArtistID
	WHERE CHARINDEX('~' + CAST(A.ArtistID AS varchar) + '~', @ArtistIDs) > 0
	ORDER BY A.SortOrder, c.SortOrder, t.Title

	SELECT  @myhtmlfile = @mydocdir + @mywebdir + @mycdsdir + @desc + '.htm',
		@querystr ='
SELECT ''' + @Names + '''
SELECT Comments FROM Artists
WHERE CHARINDEX(''~'' + CAST(ArtistID AS varchar) + ''~'',
		''' + @ArtistIDs + ''') > 0 And
	Comments IS NOT NULL
SELECT URL, Name FROM Artist_URLs
WHERE CHARINDEX(''~'' + CAST(ArtistID AS varchar) + ''~'',
		''' + @ArtistIDs + ''') > 0
SELECT CDOrder, Title, Artist
FROM ##C c
ORDER BY CDOrder'

	EXECUTE sp_makewebtask @outputfile = @myhtmlfile, @query = @querystr,
			@templatefile = @mytplfile, @dbname = 'My Personal Db'
	
	TRUNCATE TABLE ##C
	TRUNCATE TABLE #T
END
DEALLOCATE Artists
DROP TABLE ##A
DROP TABLE ##C
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateHTMLForCDs'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.CreateHTMLForCDs To Public
    PRINT '<<< CREATED PROCEDURE dbo.CreateHTMLForCDs >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.CreateHTMLForCDs >>>'
go