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