Set Approach instead of Cursor

[Hareesh]

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*/
/***********************************/

[Simon Hayes]

"Hareesh" <Ha***********@Rapistan.com> wrote in message
news:e5*************************@posting.google.co m...

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

<snip>

Thanks for the sample code, but it would be useful to know the keys of the
tables. Assuming that (Project, TagName) and (Project, KeyName) are the
keys, then this is one possibility:

update ProductDetails
set Value = gt.Value
from ProductDetails pd
join GlobalTags gt
on gt.Project = pd.Project
and gt.TagName = pd.KeyName
where charindex('%', pd.Value) > 0
and pd.Project = 'ProjectName'

Simon

[Hareesh]

Hi,

There has been a misunderstanding.
Let me clarify the problem.

The tables are as they are, with no keys.
There is no relationship between GlobalTags.TagName and
ProductDetails.KeyName
Basically the TagName from GlobalTags will appear in the Value field
of ProductDetails, that particular occurence of the TagName has to be
replaced with the value from the GlobalTags.

Let me add two more records to make the problem more clear.

INSERT INTO ProductDetails (Project, KeyName, Value)
VALUES('ProjectName', 'Name', 'xxx%FirstName%')
INSERT INTO ProductDetails (Project, KeyName, Value)
VALUES('ProjectName', 'Name', 'yyy%LastName%')

Hareesh

[--CELKO--]

>> The tables are as they are, with no keys. <<

Then **by definition** these are not tables. And you cannot process
them in an RDBMS environment. Using SQL for sequential file
processing is a waste of resources.

There is no relationship between GlobalTags.TagName and ProductDetails.KeyName <<

A relational database is called "relational" because there ARE
relationships!
Basically the TagName from GlobalTags will appear in the Value

field [sic]
of ProductDetails, that particular occurence of the TagName has to be
replaced with the value from the GlobalTags. Let me add two more
records [sic] to make the problem more clear. <<

Rows are not records; fields are not columns; tables are not files;
there is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless.

You need to read a book on SQL and RDBMS; everything you have done is
fundametnally wrong.

[Hareesh]

Joe,

You need to read a book on SQL and RDBMS; everything you have done is
fundametnally wrong.

I am aware of SQL and RDBMS, thank you.
I do not need unsolicited advice.
Stick to the problem.

I do agree that this is file processing than relational database processing.
The problem is because of what I inherited.

Hareesh

[Joe Celko]

>> I do not need unsolicited advice. <<

Then why are you posting to newsgroups?

Stick to the problem. <<
The problem IS an unusable design
I do agree that this is file processing than relational database processing. <<

Then do it in a language that supports file processing, a front end host
application.

I have kludged this kind of problem before; formatting data onthe server
side in violation of basic C/S design.

It eats resources on the server side and the code becomes very complex
when NULLs are taken into consideration. Changes in the application,
such as the width of the display lines, cascade back to the server side.

Based on my experience, this is not the way to solve the problem.
The problem is because of what I inherited. <<

Fire the person who did this, and re-write all his code.
Start over; do it right. I have charged people thousands of dollars for
exactly that advice :)

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hareesh]

> Then why are you posting to newsgroups?
To find if there was some way to perform in a
Relational Database way which I missed.

Fire the person who did this, and re-write all his code.
Start over; do it right.

I wish I could do that. Believe me.
It is too late.
It is very difficult to educate people without an
understanding of Relational Database, more difficult
if they are in a higher position and have been in
the company longer and had already impressed
the more ignorant higher ups.