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.

Improve this question
11
  • 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.... Commented Oct 29, 2014 at 6: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. Commented Oct 29, 2014 at 6:17
  • Also, try executing the Stored procedure first from SSMS. If that run fines, then problem is in you c# code Commented Oct 29, 2014 at 6: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 Commented Oct 29, 2014 at 6: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. Commented Oct 29, 2014 at 6:21

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I am not able to execute the dynamic query...that's the problem
when there is small string it is working fine....so there is no issue in SP except that nvarchar issue
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.
Are you able to post the entire SP?
See if this stackoverflow post helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.