Using LOB datatypes

Example 1

1. Create table_1:

   create table t1 (a1 int, a2 text)
       insert into t1 values(1, "aaaa")
       insert into t1 values(2, "bbbb")
       insert into t1 values(3, "cccc")
   

2. Create a stored procedure using an LOB local variable as a parameter:

   create procedure my_procedure @new_var text
       as select @new_var
   

3. Declare the local variable and execute the stored procedure.

   declare @a text
   select @a = a2 from t1 where a1 = 3
   exec my_procedure @a
   

   ---------------------------
   cccc
   

Example 2

declare @a text
select @a = "abcdefgh"
select datalength(@a)

--------------
              8

Example 3

declare @a text
select @a = '<doc><item><id>1</id><name>Box</name></item>'
    +'<item><id>2</id><name>Jar</name></item></doc>'
select id from xmltable ('/doc/item' passing @a
    columns id int path 'id', name varchar(20) path 'name')
as items_table

 id
-----------
          1
          2

And then passes the same LOB parameters to a stored procedure:

create proc pr1 @a text
as
select id from xmltable ('/doc/item' passing @a
columns id int path 'id', name varchar(20) path 'name') as items_table
declare @a text
select @a =
'<doc><item><id>1</id><name>Box</name></item>'
+'<item><id>2</id><name>Jar</name></item></doc>'

 id
-----------
          1
          2