How to manage SQL Server text with more than 8000 characters without errors? VARCHAR and NVARCHAR fields are limited to 8000 characters with SQL Server. Handle text fields longer than 8000 characters and you get the following message: “The text, ntext, and image data types are invalid for local variables.
Here is a solution to get around this 8000 character limitation in T-SQL with SQL Server.
How to handle SQL Server text with more than 8000 characters ?
First of all, this error appears if you tried to declare an argument of type TEXT in a stored procedure as follows:
CREATE PROCEDURE MY_PROCEDURE @Variable_Text TEXT AS BEGIN DECLARE @VARIABLE_TEXT TEXT -- The problem is in this line DECLARE @VARIABLE_VARCHAR VARCHAR(8000) -- Type VARCHAR limited to 8000 characters -- SQL CODE continuation of the Stored Procedure -- ETC. END;
The solution to avoid the SQL Server limitation of 8000 characters is the VARCHAR(MAX) type
Secondly, Microsoft SQL Server does not allow the syntax seen above.
Avoid the common error “The text, ntext, and image data types are invalid for local variables.” ?
In some time, the TEXT type will disappear from the Microsoft DBMS as explained in the official MS documentation page about ntext, text and image datatypes.
In order to avoid this problem you will have to use the NVARCHAR(MAX) type. It gives the possibility to store strings of variable length. And thus to contain strings higher than 8000 characters.
Indeed, here is an example of SQL code in 3 steps to:
- Delete the procedure if it exists.
- Create the temporary table.
- Create the stored procedure that uses the VARCHAR(MAX) data type.
-- 1. Delete the stored procedure if it already exists in the SQL Server database IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'My_Procedure' AND type = 'P') DROP PROCEDURE My_Procedure; GO -- 2. Create the temporary target table using a field of type TEXT. CREATE TABLE #Temp_Table ( VERY_LONG_FIELD TEXT ); -- 3. create the SQL stored procedure that handles long text fields CREATE PROCEDURE MY_PROCEDURE AS BEGIN DECLARE @Varchar_Variable VARCHAR(MAX); DECLARE @i INTEGER; SET @Varchar_Variable = 'A'; SET @i = 0; PRINT 'TEST ' + @Varchar_Variable; WHILE (@i < 10000) BEGIN SET @Varchar_Variable = @Varchar_Variable + 'A'; SET @i = @i + 1; END PRINT @Varchar_Variable; INSERT INTO #Temp_Table VALUES (@Varchar_Variable); END;
Storing a SQL Server text of more than 8000 characters in a column of type TEXT
-- The execution of the stored procedure seen above EXEC MY_PROCEDURE; -- The SELECT query to check the result SELECT VERY_LONG_FIELD FROM #Temp_Table;
Finally, this solution makes it possible to manipulate text fields of considerable length. Note also that it is possible to use a field of type NVARCHAR(MAX) to store the result of the query. To go further, it is also possible to use the SQL Server XML format to transform XML content into text with separators.