How do I pass a string parameter greater than varchar(8000) in SQL Server 2000?

You get a compilation error if you define the string parameter to have a size greater than 8000

e.g.

The size (9000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

Any ideas?

From stackoverflow

• you need to store it as TEXT instead of varchar for string larger than 8000 in sql 2000

• You can't do that in SQL 2000, use the "text" data type instead.

  Choose SQL 2000 Data Types

• You can't use text as a parameter value for a stored proc in SQL 2000, so usually the technique is to break up the information into chunks of 8000 characters or less and reassemble in the proc.

• You can't use text as a parameter value for a stored proc in SQL 2000<< Sure you can.

  What you cannot do is define a local variable as text