Re^2: Appending text to a column in SQL

Replies are listed 'Best First'.
Re^3: Appending text to a column in SQL by imp (Priest) on Oct 18, 2006 at 03:26 UTC
I used a stored procedure for encapsulating that behaviour when I had a similar problem in the past, but if you don't have permission to add stored procedures you should be able to run the sequence of commands inline. I don't have access to a server to test with currently though, so your mileage may vary. Something like this: `my $sql = 'DECLARE @ptrval binary(16);' . ' SELECT @ptrval = TEXTPTR(item)' . ' FROM table ' . ' WHERE whatever criteria;' . ' UPDATETEXT table.item @ptrval NULL 0 ?'; $dbh->do($sql,{},"new text");` [download] But of course that's horribly ugly. So, a stored procedure to encapsulate the behaviour: `CREATE PROCEDURE dbo.APPEND_THINGY @id int, @new_text varchar(8000) AS DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(item) FROM table WHERE id = @id UPDATETEXT table.item @ptrval NULL 0 @new_text; GO` [download] And then in your code: `my $sql = q{exec append_thingy @new_text = ?}; $dbh->do($sql,{},"new text");` [download]	[reply] [d/l] [select]
Re^4: Appending text to a column in SQL by sgifford (Prior) on Nov 01, 2006 at 17:42 UTC
Thanks imp! I finally got to my client's site to try that code out, and it worked perfectly! -- sgifford's Web page	[reply]
Re^4: Appending text to a column in SQL by Anonymous Monk on May 27, 2008 at 17:14 UTC
CREATE PROCEDURE dbo.APPEND_THINGY @id int, @new_text varchar(8000) AS Update table SET field = cast(field as varchar(max)) + @new_text where id = @id GO	[reply]