NVarChar 4000 limit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NVarChar 4000 limit

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2008-03-03 : 22:32:33

I'm using a function which splits a comma delimitted list of numbers which I call from a stored procedure. The list is defined as a NVarChar which has a maximum length of 4000 characters. I often need to pass more than 4000 characters. If I do so, I receive the error message below. Is there any way around this limitation? Thanks.

Msg 257, Level 16, State 2, Procedure up_ExportQuickSearchresults, Line 0
Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query.

CREATE FUNCTION dbo.Split
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (IDDir NVARCHAR(4000))
AS

BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @IDDir NVARCHAR(4000)

SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @IDDir = @tempItemList
ELSE
SET @IDDir = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(IDDir) VALUES(@IDDir)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

CREATE PROCEDURE up_ExportSelectedDirectors
@p_selectedDirectors NVARCHAR(4000)
AS
BEGIN

SELECT * FROM v_SearchResultsDirectors
WHERE IDDir IN (SELECT IDDir FROM split(@p_selectedDirectors, ','))
END
GO

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-03 : 23:32:04

Can try nvarchar(max) in sql2k5.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-04 : 01:26:41

Hi,

try with this

CREATE PROCEDURE up_ExportSelectedDirectors
(
@p_selectedDirectors NVARCHAR(4000)
)
AS
BEGIN

DECLARE @p_selectedDirectors NVARCHAR(MAX),
@delimiter NVARCHAR(5)
DECLARE @Table TABLE (Id varchar(1000))
SELECT @p_selectedDirectors = '1,2,3,6,7,8,R,a,n,g,a,n,a,t,h',
@delimiter = ','

DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@p_selectedDirectors, @delimiter, '</d><d>') + '</d>' AS XML);
INSERT INTO @Table
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)
SELECT * FROM @Table

SELECT * FROM v_SearchResultsDirectors
WHERE IDDir IN (SELECT id FROM @Table )
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-04 : 07:48:53

Also why dont use use varchar than nvarchar?

Madhivanan

Failing to plan is Planning to fail

evanburen
Posting Yak Master

167 Posts

Posted - 2008-03-04 : 08:52:32

Thank you but I'm still using SQL Server 2000 so I don't think using MAX will work for me.

evanburen
Posting Yak Master

167 Posts

Posted - 2008-03-04 : 08:55:01

Thanks. I changed it to varchar(8000) which DOES help a lot but I sometimes pass values longer than this also.