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.
Table of Contents
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:
- 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;
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.