Examples for Using LOB Datatypes

Use LOB datatypes as the input parameter for a stored procedure, or in a text function.

Example 1

Uses an LOB as the input parameter for a stored procedure:
  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

Uses an LOB variable in a text function:
declare @a text
select @a = "abcdefgh"
select datalength(@a)
--------------
              8

Example 3

Declares an LOB text local variable:
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