views:

188

answers:

2

In a stored procedure I'm trying to conditionally update a field (like the 2nd line in the SQL statement below)

UPDATE [some_stuff] SET
  last_update = CASE WHEN val = @NewVal THEN last_update ELSE GETDATE() END,
  val = @NewVal

...but for a text/ntext field. What's the most efficient way to go about doing that? Does it have to be a separate UPDATETEXT statement? And do I have to do an extra SELECT first?

+3  A: 

The above example will work in SQL Server 2005 where val is a Text field and you're updating the whole value. If you're only replacing part of a field then use UPDATETEXT in a separate statement.

A better solution, if you can update the schema is to use VARCHAR(MAX) or NVARCHAR(MAX) columns. The UPDATETEXT command has been marked as deprecated in a future release of SQL Server. If you're using these data types then you can use the column_name.WRITE in the UPDATE statement to replace part of a value.

Dave Barker
I need to append text to the end of the text field. The solution has to work on MS SQL 2000-2008. So converting to VARCHAR(MAX)/NVARCHAR(MAX) is out.
fung
A: 

Maybe the example isn't realistic, but couldn't you simplify this down to:

UPDATE [some_stuff] 
SET last_update =  GETDATE()
WHERE val != @NewVal

The first half of the case just sets the field to itself, which seems kinda pointless.

JohnFx
@John, that's actually just part of a larger query. Some other fields need to be updated as well conditionally based on some other fields so that's why I can't put it as a WHERE clause unless I split up the updates.
fung