CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
David
David

Reputation: 2569

MS SQL Update within a loop

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

Upvotes: 0

Views: 1876

Answers (4)

Ken Keenan
Ken Keenan

Reputation: 10578

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.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416159

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

Upvotes: 0

tekBlues
tekBlues

Reputation: 5793

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.

Upvotes: 1

Michał Chaniewski
Michał Chaniewski

Reputation: 4816

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.

Upvotes: 6

Related Questions