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