SQL Server text with more than 8000 characters

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:

  1. Delete the procedure if it exists.
  2. Create the temporary table.
  3. 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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*