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:

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")

Create a stored procedure using an LOB local variable as a parameter:
```
create procedure my_procedure @new_var text
    as select @new_var
```

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