I am using a stored procedure to take information from one table (a 
varchar field), set it to a local variable, then run a stored procedure on 
it which unescapes unsafe characters ( '%20' is converted to ' ' etc.). I 
am then updating the column with the new unescaped data.

It is working fine, but now I need to do the same with a text field. It 
says I cannot assign a text data type to a local variable. How can i get 
around this?

Cheers

declare @mystring as varchar(8000)
declare @myretstring as varchar(8000)
declare @myint as int
set @myint = 0

while @myint < 9999  
begin

set @mystring = (select heading from tblsearch where searchid= @myint) 
EXEC sp_unescape2 @mystring, @myretstring OUTPUT

update tblsearch set heading = @myretstring where searchid = @myint

set @myint = @myint + 1
continue
end