MS SQL Update within a loop

Question

Hi,

im trying to realize something like this:

DECLARE @id int
declare @cont varchar(max)
declare @temp varchar(max)
DECLARE curs CURSOR FOR SELECT id, [content] FROM [dbo].[Contents]

OPEN curs

FETCH NEXT FROM curs into @id, @cont
WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @temp = replace(@cont, @param_ReplaceThis, @param_WithReplacement)

      update [dbo].[Contents]
         set [Content] = @temp
        where id = @id;

   FETCH NEXT FROM curs into @id, @cont;
   END;
CLOSE curs;
DEALLOCATE curs;

but i always get the errormessage that it's not allowed to use 'UPDATE' within a function... i only that this stuff is working fine on Oracle...

Answer 1

Why use cursor for something that basicaly is a simple set operation?

UPDATE [dbo].[Contents] 
SET [Content] = replace([Content], @param_ReplaceThis, @param_WithReplacement)

Answering your question further, you can add FOR UPDATE [ OF column_name, ... ] to your DECLARE CURSOR statement. If you omit [ OF column name, ... ] then all columns will be updateable.

Answer 2

Are you creating it as a stored procedure:

CREATE PROCEDURE ?? or as CREATE FUNCTION ??

You must use CREATE PROCEDURE to allow that kind of procedural logic.

And +1 to the unnecessary cursor comment.

Answer 3

Sounds like you're building this as a User Defined Function when you mean to be building a stored procedure.

Answer 4

User-defined functions in SQL Server cannot alter database state. You have to use a stored procedure; if you need to return a value, use an OUTPUT parameter.