create procedure #t (
@t1 text , @i1 image, @t2 text, @i2 image, @t3 varchar(30), @i3 varbinary(2)
)
as
declare @tptr varbinary(16), @iptr varbinary(16), @tpos int, @ipos int
create table #blob(id int identity, t text, i image)
insert #blob values(@t1, @i1)
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
update #blob set t = @t2, i = @i2 where id = @@identity
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
select @tptr = TEXTPTR(t), @tpos = PATINDEX('%TEXT...%', t) - 1,
@iptr = TEXTPTR(i), @ipos = 2
from #blob
updatetext #blob.t @tptr @tpos 0 @t3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
updatetext #blob.i @iptr @ipos 1 @i3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
go
exec #t 'SOME TEXT HERE...', 0x02498765bcde3,
'MODIFIED TEXT...', 0xab86ec64,
'(INSERT BEFORE TEXT) ', 0xcd -- replace 3rd byte
-- Inserted text & image value
/* id text_val image_val
----------- -------------------------------------------------- -----------------
1 SOME TEXT HERE... 0x002498765BCDE3
*/
-- Updated text & image value. This one replaces the existing values
/*
id text_val image_val
----------- -------------------------------------------------- -----------------
1 MODIFIED TEXT... 0xAB86EC64
*/
-- Modified text value only. This one inserts some text into the existing value
/*
id text_val image_val
----------- -------------------------------------------------- -----------------
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86EC64
*/
-- Modified image value only. This one changes a byte in the existing value
/*
id text_val image_val
----------- -------------------------------------------------- -----------------
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86CD64
*/
go
drop proc #t
go
This page was last updated on May 01, 2006 04:28 PM.