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 : https://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
This page was last updated on May 01, 2006 04:28 PM.