January 27, 2011 at 10:56 pm
Hi,
I have a stored procedures with 5 Parameters
Example:
Create Proc Test
(
@parameter1 as varchar(max),
@parameter2 as varchar(max),
@parameter3 as varchar(max),
@parameter4 as varchar(max),
@parameter5 as varchar(max)
)
as
-----------
begin
Statements .............
--------
end
When i am sending the @parameter1 values more than 8000 character then the SQL server taking only 8000 char. then Procedures failed to execute
Note: My parametes is an pure SQL Statements like Huge Update or Insert query..
Is there any possible to make my parameter with >8000 charaters
January 27, 2011 at 11:13 pm
Hi
You can consider using nvarchar(max).
January 27, 2011 at 11:19 pm
But nvarchar maximum size is 4000 right?
January 27, 2011 at 11:21 pm
Yes..you are right..Sorry for that..
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
With the column defined as VARCHAR(MAX), it can accept a string longer than 8,000 characters. But with the CHECK constraint, it will not allow a string with a length of more than 10,000 characters. To verify that the column will not allow a string of more than 10,000 characters, try executing the following INSERT command, which will insert a string value of 11,000 characters:
INSERT INTO [dbo].[VarChar10000] ( [VarChar10000] )
VALUES (REPLICATE(CAST('A' AS VARCHAR(MAX)), 11000))
The following error will be shown when the INSERT statement above is executed:
Server: Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the CHECK constraint "MaxLength10000".
The conflict occurred in database, table "dbo.VarChar10000", column 'VarChar10000'.
The statement has been terminated.
January 27, 2011 at 11:56 pm
Hi
Has it solved ur problem..??
January 31, 2011 at 10:01 pm
If your parameters are varchar(max), you should have no problems passing values longer than 8000 bytes. Can you provide some information on how you call the procedure, and how it processes the parameters?
This works fine on SQL Server 2008 R2:
create procedure sp_test @par1 varchar(max), @par2 varchar(max)
as
select len(@par1), len(@par2)
go
declare @v-2 varchar(max) = replicate(cast('a' as varchar(max)), 10000)
go
The procedure returns 10000 as the lengths of both parameters; they are not truncated.
February 1, 2011 at 12:31 am
nvarchar(max) can store upto 2GB
February 1, 2011 at 12:51 am
You cannot have a nvarchar(n) type where n exceeds 4000. the only way to store more than 8000 bytes is to use nvarchar(max). It is also not true that it has a fixed size - it's a variable-length type and can hold up to 2 GB of data.
February 1, 2011 at 1:46 am
You are right, i was having the doubt on the same but i cross verified the same.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply