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  :	http://www.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