IF OBJECT_ID('dbo.RefreshCDsData') IS NOT NULL And
        OBJECTPROPERTY(OBJECT_ID('dbo.RefreshCDsData'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.RefreshCDsData
    IF OBJECT_ID('dbo.RefreshCDsData') IS NOT NULL And
                OBJECTPROPERTY(OBJECT_ID('dbo.RefreshCDsData'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RefreshCDsData >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RefreshCDsData >>>'
END
go
CREATE PROCEDURE RefreshCDsData
--WITH ENCRYPTION
AS
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    3 Feb 2000                                              */
/*      Description:    This SP is used to refresh the data for the CDs table   */
/*                      from the Windows 2000 DeluxeCD database. A direct join  */
/*                      on the Artist field against the [DeluxeCD Titles] view  */
/*                      (distributed query) and Artists table doesn't work. This*/
/*                      workaround uses CHARINDEX instead.                      */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
INSERT CDs
SELECT TitleId, A.ArtistID, '', 0
FROM [DeluxeCD Titles] t JOIN Artists A
ON CHARINDEX(A.Artist, t.Artist) > 0
WHERE NOT EXISTS(SELECT * FROM CDs c WHERE c.TitleID = t.TitleID And c.ArtistID = A.ArtistID)
go
IF OBJECT_ID('dbo.RefreshCDsData') IS NOT NULL And
        OBJECTPROPERTY(OBJECT_ID('dbo.RefreshCDsData'), 'IsProcedure') = 1
    PRINT '<<< CREATED PROCEDURE dbo.RefreshCDsData >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RefreshCDsData >>>'
go
GRANT EXECUTE ON dbo.RefreshCDsData To Public
go

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