Reputation: 2569
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
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
Reputation: 416159
Sounds like you're building this as a User Defined Function when you mean to be building a stored procedure.
Upvotes: 0
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
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