Using large object text, unitext, and image datatypes in stored procedures

With Adaptive Server version 15.7 and later, you can declare a large object (LOB) text, image, or unitext datatype for a local variable, and pass that variable as an input parameter to a stored procedure. Prior to version 15.7, a named parameter for text, image, or unitext data in a stored procedure could not exceed the current page size: 2, 4, 8, or 16KB.

This example uses an LOB datatype in a stored procedure:

1. Suppose we 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 a LOB local variable as a parameter:

   create procedure my_procedure @loc text
   		as select @loc
   

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
   

Certain restrictions apply. An LOB datatype:

• Cannot be used as an output parameter of a stored procedure

• Cannot be used in a datatype conversion using the convert() function

• Is not supported for replication