Click here to Skip to main content
14,333,606 members
Rate this:
Please Sign up or sign in to vote.
I am using SQL server 2008.In Stored Procedure (SP) I have string type variables. Now I want to store some string type value in this variable. But if the string size is less than 4000 then it works nicely. But when the size is over 4000 then it does not work.


SET @SQLString='Some SQL SELECT statemtnt and its size (len) more than 4000... Suppose its size is 10000 '

If I use
DECLARE @SQLString varchar(10000)
Then I found some error like- The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

Can anyone to help me to overcome this problem.

Thanks in advance
Updated 24-Aug-19 0:13am
Rate this:
Please Sign up or sign in to vote.

Solution 1

From Frequently Asked Questions - SQL Server 2005 - VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)[^] ->

"To create a column of VARCHAR data type with a maximum of 10,000 characters, you declare the column as VARCHAR(MAX) data type and simply add a CHECK constraint to the column by checking the length of the column and making sure it is less than or equal to 10,000 characters. To illustrate, here's how it will look like:

CREATE TABLE [dbo].[VarChar10000] ( [VarChar10000] VARCHAR(MAX) )

ALTER TABLE [dbo].[VarChar10000] 
    ADD CONSTRAINT [MaxLength10000]
    CHECK (DATALENGTH([VarChar10000]) <= 10000)
Rasadul Alam Rashed 4-Apr-12 4:32am
But i will use it in sql stored procedure for variable declaration. Not in table creation..
Monjurul Habib 6-Apr-12 5:17am
Abhinav S 6-Apr-12 6:30am
Thank you Monjurul.
Rasadul Alam Rashed 11-Apr-12 1:40am
For what??
Rate this:
Please Sign up or sign in to vote.

Solution 3

You Could Try

Declare @SQL as nvarchar(Max)
	,@SQL2 as nvarchar(Max)

-- Just Sample
-- each variable max 4000 character, so be wise in splitting your syntaxs

Set @SQL  = 'Select * '
Set @SQL2 = 'From table A'


Note if your dynamic string lenght more than > 8000 character you should split into 3 variables'

this should works, because i was have same problem
Rate this:
Please Sign up or sign in to vote.

Solution 2

better used
DECLARE @SQLString text

Let me know if it helps


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100