Using LOB datatypes

Example 1

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

Example 2 Uses an LOB variable in a text function:

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

Example 3

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