What is the biggest data type to use as a local variable in a stored procedure?

Question

I have the next issue:

--DECLARE @TEST NVARCHAR(MAX)
--DECLARE @TEST2 NVARCHAR(MAX)

DECLARE @TEST NTEXT
DECLARE @TEST2 NTEXT

NVARCHAR(MAX) is to small for the amount of text in need to put when executing a stored procedure, also, TEXT, NTEXT and IMAGE data types are invalid for local variables, what can I do to sidestep this issue and store the oversized text like.

Thanks in advance

Solution

NVARCHAR(MAX) is to small for the amount of text in need to put when executing a stored procedure

Well, bad news: this is the largest data type available! 2GB of storage, there just isn't anything that can hold more than that. In fact all large types have the same size: VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX): they all have 2GB max size (As a side note the deprecated legacy types have exactly the same max size). Only FILESTREAM can exceed this size, but you cannot declare a variable as FILESTREAM.

So this really begs the question: what the heck are you doing in a stored procedure to add +2GB of data in a variable? You cannot possible have a justified reason for this, so you should reconsider your approach. Use the disk, Luke, not the RAM! Consider a @table variable or a #temp table...