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:

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

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

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