Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Question

Hi All,

I have the following query :

select P.ProjectName, T.CaseUID, T.CaseID, T.TypeID, T.ProjectID
from [CaseMaster] T
join [ProjectsMaster] P
on T.ProjectID = P.ProjectID
where CAST(P.ProjectName as varchar(10)) + '|' + CAST(T.TypeID as varchar(10)) IN ('01_006_1|1','01_007_1|1','01_007_1|3','01_007_1|15')
order by P.ProjectName

Please note I have many values after IN. When I execute the above query, I am getting the error message:

"Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them."

What I got to know is I have split into multiple query. But I am not sure how can I split into multiple queries.

Can you please let me know how can I resolve this issue ?
Kindly waiting for your response.

Thanks,
Santosh

Answer 1

The Microsoft documentation "MSSQLSERVER_8632" says

This issue occurs because SQL Server limits the number of identifiers and constants that can be contained in a single expression of a query. This limit is 65,535.

I think the issue in your query is the IN statement since you said there are many values listed in the IN statement. I would suggest to put those values into the TABLE variable and then use the SELECT statement in the IN statement:

DECLARE @ProjectName_TypeIDs TABLE (  
	ProjectName_TypeID varchar(21)  
);  
  
INSERT INTO @ProjectName_TypeIDs (ProjectName_TypeID) VALUES  
('01_006_1|1'), ('01_007_1|1'), ('01_007_1|3'), ('01_007_1|15')   
  
select P.ProjectName, T.CaseUID, T.CaseID, T.TypeID, T.ProjectID  
from [CaseMaster] T  
join [ProjectsMaster] P  
on T.ProjectID = P.ProjectID  
where CAST(P.ProjectName as varchar(10)) + '|' + CAST(T.TypeID as varchar(10)) IN (SELECT ProjectName_TypeID FROM @ProjectName_TypeIDs)  
order by P.ProjectName  

Answer 2

Hi @Santosh Umarani ,

To work around this issue, rewrite your query. Reference fewer identifiers and constants in the largest expression in the query. You must make sure that the number of identifiers and constants in each expression of the query does not exceed the limit. To do this, you may have to break down a query into more than one single query. Then, create a temporary intermediate result.

Or try:

select P.ProjectName, T.CaseUID, T.CaseID, T.TypeID, T.ProjectID  
from [CaseMaster] T  
join [ProjectsMaster] P  
on T.ProjectID = P.ProjectID  
where CAST(P.ProjectName as varchar(10)) + '|' + CAST(T.TypeID as varchar(10)) = '01_006_1|1'  
Or CAST(P.ProjectName as varchar(10)) + '|' + CAST(T.TypeID as varchar(10)) ='01_007_1|1'  
Or CAST(P.ProjectName as varchar(10)) + '|' + CAST(T.TypeID as varchar(10)) ='01_007_1|3'  
Or CAST(P.ProjectName as varchar(10)) + '|' + CAST(T.TypeID as varchar(10)) ='01_007_1|15'  
order by P.ProjectName  

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Yes ErlandSommarskog, I have not added these values from hand. I have got these values from string value from C# code.

var listOfActivatedProjects = string.Join(",", listOfProjectWithTestType.Select(x => string.Format("('{0}|{1}')", x.Key,x.Value)));

That's not the best way to do it. (As you may have realised by now.) A much better way is to pass the values in a table-valued parameter. I have an article on my web site that gives you all the details you need. One of the examples is exactly to send a list of scalar values. https://www.sommarskog.se/arrays-in-sql-2008.html

Answer 4

Hi @Santosh Umarani ,

To solve this problem,you can refer to the following three methods:

Listing the VALUES clauses in a CTE, then INSERTing from the CTE.

Or break the INSERT statement up into VALUES clauses of <= 1,000 lines each .

Or use bcp or BULK INSERT.

Please also refer to:
Multiple INSERT statements vs. single INSERT with multiple VALUES

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.