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