0

I have created a stored procedure in a SQL Server 2008 database with a parameter variable of type Nvarchar(MAX) and from the C# code I am passing a string with length of more than 4000 characters. As it is truncating that string to 4000, I am not able to execute that procedure successfully.

So, is there any way to extend that limit?

I have passed following string through parameters

@WhereClause = N' ((CHARINDEX('',137,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',138,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',139,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',140,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',141,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',142,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',143,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',144,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',145,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',146,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',147,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',438,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',439,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',440,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',784,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',845,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1028,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1076,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1081,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1114,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1122,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1129,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1241,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1432,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1112,'','',''+ Edu +'','') > 0 ) ) OR ((CHARINDEX('',137,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',138,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',139,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',140,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',141,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',142,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',143,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',144,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',145,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',146,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',147,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',438,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',439,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',440,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',784,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',845,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1028,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1076,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1081,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1114,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1122,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1129,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1241,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1432,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1111,'','',''+ Edu +'','') > 0 ) ) OR ((CHARINDEX('',137,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',138,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',139,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',140,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',141,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',142,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',143,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',144,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',145,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',146,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',147,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',438,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',439,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',440,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',784,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',845,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1028,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1076,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1081,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1114,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1122,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1129,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1241,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',1432,'','',''+ Edu +'','') > 0 AND  CHARINDEX('',1113,'','',''+ Edu +'','') > 0 ) )',
    @WhereClauseEduName = N' ((CHARINDEX('',''''B.Com.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Accounts / Audit'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Banking'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Comp. Science'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Transport'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Cotton'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Business Maths'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Co-operation'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Communication'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Public Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Business Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Book-keeping'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Co-operative Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Computer Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com.ALL'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. With Costing'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Comp.Application '''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Office Management & S.P.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Buisness Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Advan.Statistics'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Statt.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Insurance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In A/c & Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.COM. IN MARKETING'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Data Entry Operator'''','','',''+ Edu +'','') > 0 ) ) OR ((CHARINDEX('',''''B.Com.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Accounts / Audit'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Banking'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Comp. Science'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Transport'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Cotton'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Business Maths'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Co-operation'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Communication'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Public Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Business Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Book-keeping'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Co-operative Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In ''Computer Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com.ALL'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. With Costing'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Comp.Application '''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Office Management & S.P.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Buisness Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Advan.Statistics'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Statt.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Insurance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In A/c & Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.COM. IN MARKETING'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer'''','','',''+ Edu +'','') > 0 ) ) OR ((CHARINDEX('',''''B.Com.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Accounts / Audit'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Banking'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Comp. Science'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Transport'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Cotton'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Business Maths'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Co-operation'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Communication'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Public Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Business Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Book-keeping'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Co-operative Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Computer Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com.ALL'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. With Costing'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Comp.Application '''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Office Management & S.P.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Buisness Management'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Advan.Statistics'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Statt.'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In Insurance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.Com. In A/c & Finance'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) OR (CHARINDEX('',''''B.COM. IN MARKETING'''','','',''+ Edu +'','') > 0 AND  CHARINDEX('',''''Computer Programming'''','','',''+ Edu +'','') > 0 ) )',
    @AreaWhereClause = N' AND candidates.DistrictId = 14',
    @VacId = 84,
    @VacancyBookNo = N'141201400049',
    @IsView = 0

Then in SP I am printing the variable @WhereClauseEduName it is giving me following result

 ((CHARINDEX(',''B.Com.'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Accounts / Audit'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Banking'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Comp. Science'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Transport'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Cotton'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Business Maths'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Co-operation'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Communication'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Public Finance'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Business Finance'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Book-keeping'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Co-operative Management'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Computer Management'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com.ALL'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. With Costing'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Comp.Application '',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Office Management & S.P.'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Buisness Management'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Advan.Statistics'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Statt.'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Insurance'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In A/c & Finance'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.COM. IN MARKETING'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Data Entry Operator'',',','+ Edu +',') > 0 ) ) OR ((CHARINDEX(',''B.Com.'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Computer'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Accounts / Audit'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Computer'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Banking'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Computer'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Comp. Science'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Computer'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Transport'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Computer'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Cotton'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Computer'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Business Maths'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Computer'',',','+ Edu +',') > 0 ) OR (CHARINDEX(',''B.Com. In Co-operation'',',','+ Edu +',') > 0 AND  CHARINDEX(',''Computer'',',','+ Edu +',') > 0 ) OR

By looking at the output I can see only first 4000 characters. Thats y my dynamic query is not running properly.

Stark
  • 441
  • 9
  • 29
  • **SHOW US THE CODE!** What is the exact interface of your stored procedure, and how do you set up and call the stored procedure for your C# code? And no, there is no 4000 character limit - `NVARCHAR(MAX)` holds **up to 1 billion** characters.... – marc_s Oct 29 '14 at 06:13
  • did you check the length specified in your c# sqlParameter argument (e.g "@CategoryName", SqlDbType.VarChar, length))? As nvarchar(max) allows the size upto 2 gb. – Amitesh Oct 29 '14 at 06:17
  • Also, try executing the Stored procedure first from SSMS. If that run fines, then problem is in you c# code – anonxen Oct 29 '14 at 06:19
  • @AmiteshKumar: yes, the **size** of the columns is 2 GB, and since each Unicode character takes up 2 bytes, it's **1 billion** characters – marc_s Oct 29 '14 at 06:21
  • through my C# code string is passing perfectly. But when i am catching that string in SQL and just printing that string at that time i am seeing only first 4000 characters. – Stark Oct 29 '14 at 06:21
  • I have updated the question with my scenario.....please check it..... – Stark Oct 29 '14 at 06:27
  • When you handle the `NVARCHAR(MAX)` parameter in T-SQL, you need to be extremely careful to always make sure to cast it to `NVARCHAR(MAX)`. Otherwise, T-SQL might truncate it - but it's really only a problem of manipulation by T-SQL - the full string should be passed just fine into your stored procedure and should be able to be stored into your table! – marc_s Oct 29 '14 at 06:33
  • If i am passing small string (<4000) in parameters then it is working fine...when there is larger string (>4000) at that time problem comes....And I dnt wnt to store in Table...I wnt to xecute that query – Stark Oct 29 '14 at 06:42
  • split your string into multiple variables of <4000 size and pass it – Dgan Oct 29 '14 at 06:47
  • its a sp parameter....before i get to split it is getting truncated.. – Stark Oct 29 '14 at 06:55
  • My eyes! Seriously, there *has* to be a better way to work with this data. Maybe you can pass some of these parameters across as table-valued parameters rather than a *massive* string? Rather than carrying on trying to work with this, could you maybe tell us what *problem* you were trying to solve that led to this monstrosity? I'm almost certain that solving your current, specific problem here is not really going to "help" you overall. – Damien_The_Unbeliever Oct 29 '14 at 07:55

1 Answers1

2

The Print command in SQL Server has a 4000 character limit for NVarchar and 8000 for Varchar. See more details on MSDN in the Remarks section http://msdn.microsoft.com/en-nz/library/ms176047.aspx

It looks like you can overcome this by manually writing some code to Print chucks of the output at a time. This link has a solution which does this and also breaks on line feeds.

Here is another solution from stackoverflow link

Community
  • 1
  • 1
Shane
  • 429
  • 3
  • 6
  • I am not able to execute the dynamic query...that's the problem – Stark Oct 29 '14 at 06:37
  • when there is small string it is working fine....so there is no issue in SP except that nvarchar issue – Stark Oct 29 '14 at 06:50
  • One of the functions you are using in the SP may be limited to 4000 characters. Try executing the SP in SSMS with parameters larger than 4000 characters and see if it works. If it works there may be a problem with how you are calling the stored procedure from .NET, so post your code for doing that. – Shane Oct 29 '14 at 07:12
  • Are you able to post the entire SP? – Shane Oct 29 '14 at 07:14
  • See if [this](http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits) stackoverflow post helps. – Shane Oct 29 '14 at 07:29