MS SQL Update within a loop

Question

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...

OH NO!!! a cursor with no WHERE clause!!! please don't loop for this! use @Michał Chaniewski single update statement!!!!! — KM.
– KM., Commented Jun 9, 2009 at 18:44

Michał Chaniewski · Accepted Answer · 2009-06-09 14:42:02Z

6

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.

answered Jun 9, 2009 at 14:42

Michał Chaniewski

4,8261 gold badge20 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Yeah absolutley right, my bad... but Oracle to MS SQL conversion drives me crazy sometimes ;)

tekBlues · Accepted Answer · 2009-06-09 14:42:07Z

1

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.

answered Jun 9, 2009 at 14:42

tekBlues

5,8031 gold badge31 silver badges32 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2009-06-09 14:43:28Z

0

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

answered Jun 9, 2009 at 14:43

Joel Coehoorn

419k114 gold badges582 silver badges820 bronze badges

Comments

Ken Keenan · Accepted Answer · 2009-06-09 19:49:55Z

0

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.

answered Jun 9, 2009 at 19:49

Ken Keenan

10.8k5 gold badges35 silver badges52 bronze badges

Collectives™ on Stack Overflow

MS SQL Update within a loop

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related