LOB Datatypes in Stored Procedures

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.

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
Certain restrictions apply. An LOB datatype: