Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Administration (2000)  Using the Replace function

Author  Topic  bbryans Starting Member 2 Posts Posted - 2001-04-03 : 15:48:34 Hi all, I posted a question to the SQLTeam this morning regarding this stored procedure. I thought that the variable @x was losing its value because only the first replace was happening. I figured it out though and I thought my wasted time might help others :) The trick is to put the string with the replaceable parameters in a temp variable so that you do not overwrite the replaceable parameters permanently (i.e. nothing to replace the 2nd time through. Here is the code as an example (i cut out sections so it would fit). If anyone knows a better way to do this please let me know. CREATE PROCEDURE uspXML_insOpportunity2 @xmldoc NTEXT, @html varchar(4000) SET @html_temp = @html BEGIN /* STEP 1: CREATE temporary table */ /* STEP 2: CREATE an internal representation of the XML document. (parses xml using MSXML) */ EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc /* STEP 3: INSERT the parsed xml into the temporary table */ INSERT INTO @Opp (ClientID, MemberID, URL, ProjectID, StartDate, ExpiresDate, R1, R2, R3, R4, R5) SELECT * FROM OpenXML (@idoc, 'OpportunityRequest/Opportunity', 0) WITH ( ClientID INT '@ClientID', MemberID CHAR(255) '@MemberID', URL varchar(200) '@URL', ProjectID varchar(30) '@ProjectID', StartDate datetime '@StartDate', ExpiresDate datetime '@ExpiresDate', R1 varchar(50) '@R1', R2 varchar(50) '@R2', R3 varchar(50) '@R3', R4 varchar(50) '@R4', R5 varchar(50) '@R5' ) /* STEP 4: REMOVE xml document from memory */ EXEC sp_xml_removedocument @idoc /* STEP 5: SET variable required for Outer and Inner loops */ SET @rowcount = (SELECT COUNT(*) FROM @Opp) SET @x = 1 SET @r = 1 /* STEP 6: LOOP through records and perform replace */ /* Outermost loop */ WHILE @x < @Rowcount + 1 BEGIN /* Innermost loop */ WHILE @r < 6 BEGIN Select @x IF @r = 1 SET @value1 = (SELECT R1 FROM @Opp WHERE OppID = @x ) IF @r = 2 SET @value2 = (SELECT R2 FROM @Opp WHERE OppID = @x ) If @r = 3 SET @value3 = (SELECT R3 FROM @Opp WHERE OppID = @x ) If @r = 4 SET @value4 = (SELECT R4 FROM @Opp WHERE OppID = @x ) If @r = 5 SET @value5 = (SELECT R5 FROM @Opp WHERE OppID = @x ) SET @r = @r + 1 --select @value1, @value2, @value3 END SET @r = 1 If Len(@value1) > 0 SET @html_temp = REPLACE( @html_temp, '<R1>', @value1) If Len(@value2) >0 SET @html_temp = REPLACE( @html_temp, '<R2>', @value2) If Len(@value3) > 0 SET @html_temp = REPLACE( @html_temp, '<R3>', @value3) If Len(@value4) > 0 SET @html_temp = REPLACE( @html_temp, '<R4>', @value4) If Len(@value5) > 0 SET @html_temp = REPLACE( @html_temp, '<R5>', @value5) Update @Opp SET Message = @html_temp WHERE OppID = @x SET @x = @x + 1 SET @html_temp = @html END Select * from @Opp