Set Approach Instead Of Cursor

Hi,

I am trying a Set Approach instead of Using of Cursor (which works).

I am attaching the SQL to create tables and the my Procedure, and a
piece of code to execute the Procedure.

I would like the Procedure ReplaceTags to work with 'a' the same as
with 'C'.

Thanks in advance.

Hareesh

/*****************************/
/* Create Tables */

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =
OBJECT_ID(N'GlobalTags') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE GlobalTags
GO

CREATE TABLE GlobalTags
(
Project VARCHAR(50) NULL,
TagName VARCHAR(50) NULL,
[Value] VARCHAR(50) NULL
)
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =
OBJECT_ID(N'ProductDetails') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
DROP TABLE ProductDetails
GO

CREATE TABLE ProductDetails
(
Project VARCHAR(50) NULL,
KeyName VARCHAR(50) NULL,
[Value] VARCHAR(50) NULL
)
GO


/*********************************/
/* Populate Tables */

TRUNCATE TABLE GlobalTags

INSERT INTO GlobalTags (Project, TagName, Value)
VALUES('ProjectName', 'FirstName', 'John')
INSERT INTO GlobalTags (Project, TagName, Value)
VALUES('ProjectName', 'LastName', 'Doe')
INSERT INTO GlobalTags (Project, TagName, Value)
VALUES('ProjectName', 'PhoneNo', '5248')
INSERT INTO GlobalTags (Project, TagName, Value)
VALUES('ProjectName', 'ZIPCode', '55555')

TRUNCATE TABLE ProductDetails

INSERT INTO ProductDetails (Project, KeyName, Value)
VALUES('ProjectName', 'FirstName', '%FirstName%')
INSERT INTO ProductDetails (Project, KeyName, Value)
VALUES('ProjectName', 'LastName', '%LastName%')
INSERT INTO ProductDetails (Project, KeyName, Value)
VALUES('ProjectName', 'PhoneNo', '%PhoneNo%')
INSERT INTO ProductDetails (Project, KeyName, Value)
VALUES('ProjectName', 'ZIPCode', '%ZIPCode%')

/****************************/
/* Procedure */

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ReplaceTags')
DROP PROCEDURE ReplaceTags
GO

CREATE PROCEDURE ReplaceTags
(
@aProjectName VARCHAR(50),
@aProcessType CHAR(1)
)
AS
BEGIN

DECLARE @TagName VARCHAR(50)
DECLARE @Value VARCHAR(50)

IF @aProcessType = 'C'
BEGIN
DECLARE REPLACE_CURSOR CURSOR FAST_FORWARD READ_ONLY FOR
SELECT TagName, Value FROM GlobalTags
WHERE Project = @aProjectName

OPEN REPLACE_CURSOR
FETCH NEXT FROM REPLACE_CURSOR INTO @TagName, @Value

WHILE (@@FETCH_STATUS = 0)
BEGIN

UPDATE ProductDetails
SET
Value =
CASE WHEN CHARINDEX('%' + @TagName + '%', Value, 1) > 0
THEN
REPLACE(Value, '%' + @TagName + '%', @Value)
ELSE
Value
END
WHERE Project = @aProjectName

FETCH NEXT FROM REPLACE_CURSOR INTO @TagName, @Value
END

CLOSE REPLACE_CURSOR
DEALLOCATE REPLACE_CURSOR

END
ELSE
BEGIN
UPDATE ProductDetails
SET
Value =
CASE WHEN CHARINDEX('%' + GlobalTags.TagName + '%',
ProductDetails.Value, 1) > 0 THEN
REPLACE(ProductDetails.Value, '%' +
GlobalTags.TagName + '%', GlobalTags.Value)
ELSE
ProductDetails.Value
END
FROM ProductDetails INNER JOIN GlobalTags
ON (ProductDetails.Project = GlobalTags.Project)
WHERE ProductDetails.Project = @aProjectName

END

END

/***********************************/
/* Run Procedure */

EXECUTE ReplaceTags 'ProjectName', 'a'
-- EXECUTE ReplaceTags 'ProjectName', 'C'

SELECT * FROM GlobalTags
SELECT * FROM ProductDetails

/* End*/
/***********************************/