Take a look on the following code. It work fine with manipulation of text
variables. You can pass more than 5000 characters and revert it to int codes
to be inserted on a #temp table.

Regards - Jos Lus - Brasil

****************************************************************************
************************

CREATE PROCEDURE SP2_TesteTexto @tTurmas Text AS
Declare @iCont1 int, @pTextPtr varbinary(16),@iQTDTurmas int,
  @sSubstring varchar(10), @iConvertido int

if not exists(select * from sysobjects where id =
object_id('TempDB.#Turma'))
begin
     Create Table TempDB.#Turma (TU_Codigo int)
end
delete from TempDB.#Turma where TU_Codigo > 0

if not exists(select * from sysobjects where id =
object_id('TempDB.#Texto'))
begin
     Create Table TempDB.#Texto (TE_Codigo int,TE_Texto text)
end
delete from TempDB.#Texto where TE_Codigo > 0
Insert TempDB.#Texto values(1,@tTurmas)

select @iCont1 = 1
select @iQTDTurmas = DataLength(@tTurmas) / 10
select 'Tamanho de @tTurmas = ' = Datalength(@tTurmas)
select @pTextPtr = TEXTPTR(TE_Texto) FROM TempDB.#Texto where
#Texto.TE_Codigo = 1

While (@iCont1 <= @iQTDTurmas)
Begin
     select @sSubstring = Convert(varchar(10), TE_Texto) from #Texto
     select @iConvertido = Convert(int,@sSubstring)
     if(@iConvertido > 0)
     begin
          Insert TempDB.#Turma values(@iConvertido)
     end
     select @iCont1 = @iCont1 + 1
     UpdateText TempDB.#Texto.TE_Texto @pTextPtr 0 10
End

Select all * from TempDB.#Turma