[RESOLVED] SQL2008 R2: String or binary data would be truncated - NVARCHAR(max) LIKE clause

Hi,

I have a stored procedure that checks to see if the passed through data has changed before actually updating the table this works perfectly until the data exceeds 4000 characters despite the table field and the parameter being nvarchar(max) fields, the issue appears to be with the IsNull check, if I comment out the highlighted line (see the code block below) the SP works okay.

Does anyone know how to make "IsNull" play nicely with nvarchar(max)? Otherwise any suggestions for doing this in a better way?

Code:

---

CREATE PROCEDURE up_pUpdatePage
(
        @New_Page_Id numeric(18,0) OutPut
        ,@Page_Id numeric(18,0)
        ,@LU_Page_Id numeric(18,0)
        ,@Jnl_Code varchar(10)
        ,@Client_Code varchar(10)
        ,@Name nvarchar(250)
        ,@Title nvarchar(250)
        ,@Body nvarchar(max)
        ,@Status varchar(20)
        ,@User_Id numeric(18,0)
)
AS
SET NOCOUNT ON
DECLARE @Result int                SET @Result=@@Error
DECLARE @DT datetime                SET @DT=GetDate()
DECLARE @DateAdded datetime        SET @DateAdded=@DT
DECLARE @CurrentStatus varchar(20)        SET @CurrentStatus='A'

BEGIN TRANSACTION
IF EXISTS(SELECT NULL
                        FROM Pages WITH (UPDLOCK)
                        WHERE IsNull(Jnl_Code,'') COLLATE Latin1_General_CS_AS=IsNull(@Jnl_Code,'')
                        AND IsNull(Client_Code,'') COLLATE Latin1_General_CS_AS=IsNull(@Client_Code,'')
                        AND IsNull(Name,'') COLLATE Latin1_General_CS_AS=IsNull(@Name,'')
                        AND IsNull(Title,'') COLLATE Latin1_General_CS_AS=IsNull(@Title,'')
                        AND IsNull(Body,'') COLLATE Latin1_General_CS_AS LIKE IsNull(@Body,'')
                        AND IsNull(Status,'') COLLATE Latin1_General_CS_AS=IsNull(@Status,'')
                        AND IsNull(User_Id,-1)=IsNull(@User_Id,-1)
                        AND IsNull(LU_Page_Id,-1)=IsNull(@LU_Page_Id,-1)
                ) BEGIN
        -- This Page already exists
        SET @Result=-1
        SET @New_Page_Id=@Page_Id
END ELSE BEGIN
        -- Work out whether we can update the existing page or if we must create a new instance
....

---

Regards Al

Try writing it out the long way, which I think is this:

Code:

---

AND (  ( @Body Is Null  AND IsNull(Body,'') COLLATE Latin1_General_CS_AS LIKE '')
    OR ( @Body Is Not Null  AND IsNull(Body,'') COLLATE Latin1_General_CS_AS LIKE @Body) )

---

(but please check, my brain isn't working quite right today!)

Hi Si,

Thanks for your reply I've tried your suggestion but no luck, I've done a little more testing and it seems that it's not IsNull, I've changed the line to -

Code:

---

AND Body LIKE @Body

---

the error/warning "String or binary data would be truncated." still appears.

I was going to say, I've never had issues with varchar(max) and isnull... are you *sure* it's the Body field and not one of the others? What's the Body field definition on the Pages table look like?

-tg

TechGnome,

Thanks for your reply, I've quadripple checked it and it's a nvarchar(max)

Code:

---

Body                          nvarchar(max),

---

As a temporary workaround I'm ignoring the changed check if the Body is longer that 4000 characters, not ideal but doesn't seem to be much else that I can do, other than maybe writing a UDF to split the text up into chunks then compare those.

I notice you use a LIKE on the field, rather than an equals...

It looks like it's the like.... look at this page, all the way at the bottom, in the communuity notes....
http://technet.microsoft.com/en-us/l...9859(v=sql.100)
It talks about that 4000 character boundary causing errors to be thrown.

-tg

TechGnome,

You might have cracked it; I've changed the "like" to "=" and the error has gone away. With SQL2005 or perhaps it was 2000, I was told to use "LIKE" instead of "=" when comparing large (max) fields.

Thanks for your help.

Cheers Al

When I follow -

Quote:

---

http://technet.microsoft.com/en-us/l...9859(v=sql.100)

---

I get a page not found error.

sorry, I must not have copied it completely....

just for posterity sake... let's try it again...

http://technet.microsoft.com/en-us/l...9859(v=sql.100)

... oh... it looks like the forum flubbed up the ) on the end of the URL. ... bit.ly to the rescue!
http://bit.ly/NgvPOT

-tg