In some occasion you might want to replace some words in your database table. In this tutorial, we will show you how easily to use while loop method in SQL server.

Let's say we have the following table information

TABLE NAME = PRODUCTS
-----------------------------------------------------
ID                        |   INTEGER (AUTOMATIC GENERATED IDENTITY NUMBER)
Name                      |   NVARCHAR(100)
Value                     |   NVARCHAR(2000)
-----------------------------------------------------

We want to update the field/column value with some replacement word. So we need to create a stored procedure that will accept two values, one is the word that we are looking for and the next word is the replacement value.

Our sample procedure basically will fetch each row and update the record by passing each unique ID. We named our sample stored procedure as ReplaceData

CREATE PROCEDURE ReplaceData
	@FindWord nvarchar(2000),
	@ReplaceWord nvarchar(2000)
AS
    /** DECLARE a CURSOR **/
    DECLARE cursor_row CURSOR FOR
    SELECT ID FROM Products

	DECLARE @ID Int

	/** Open the CURSOR **/
	OPEN cursor_row
	FETCH NEXT FROM cursor_row INTO @ID
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
                /** We perform a quick update by doing a replacement **/
		UPDATE Products
		Set Value = Replace(Value, @FindWord, @ReplaceWord)
		WHERE ID = @ID

		/** Fetch the next row **/
		FETCH NEXT FROM cursor_row INTO @ID
	END
       
        /** Close and DeAllocate the CURSOR **/
	CLOSE cursor_row
	DEALLOCATE cursor_row

GO