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.

DECLARE @SQLString NVARCHAR(MAX)

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

EXECUTE (@SQLString)
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
Rashed
Posted
Updated 24-Aug-19 0:13am
v4
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) )
GO

ALTER TABLE [dbo].[VarChar10000] 
    ADD CONSTRAINT [MaxLength10000]
    CHECK (DATALENGTH([VarChar10000]) <= 10000)
GO"
   
Comments
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
   
5!
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'

EXEC (@SQL+@SQL2)


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


Regards
Ashish
   

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