SQL Server string longer than 8000 characters

How to manage SQL Server strings with more than 8000 characters without errors?

Using SQL Server string longer than 8000 characters with VARCHAR and NVARCHAR fields and explicit lenght is not possible. Indeed the explicit declarations 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. However, here is a solution to get around this 8000 character limitation in T-SQL with SQL Server.

Managing SQL Server string 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 handle the SQL Server limitation of 8000 characters for a string is to use the VARCHAR(MAX) type. Secondly, Microsoft SQL Server does not allow the syntax seen above.

The text, ntext, and image data types are invalid for local variables error

In some time, the TEXT data 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;

MS SQL string longer than 8000 characters in a TEXT column

Finally, this solution makes it possible to manipulate strings of considerable length. Note also that it is possible to use a field of type NVARCHAR(MAX) to store the result of the query.

-- 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 SQL Server string longer than 8000 characters. 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.