Feed the creative machine

Wednesday, July 02, 2008

Updating text, ntext values

While working on import utility we faced problem of maintaining big string values. We had a table with ntext field. In a stored procedure you can not declare local variable with text or ntext data types. So it was not possible to maintain big strings in stored procedure. To overcome this problem we used temporary table and used the UPDATETEXT.
See sample code.

create table t1(MyStr text)
insert into t1 values('aaaa')

CREATE TABLE #mytemp (myf text)
Insert into #mytemp values ('mayur')
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(myf) from #mytemp

UPDATETEXT #mytemp.myf @ptrval NULL 0 'End statement' ---Append at the end

UPDATETEXT #mytemp.myf @ptrval 0 0 'some more test string' ---Insert at the beginning

select #mytemp.myf from #mytemp
Update t1 set mystr = myf from #mytemp
select * from t1
drop table #mytemp