My code runs as below(more columns are there but eliminated for space )
Create function Getresults(@adminID varchar(20),@adminrole int,@condition nvarchar(max))
when 1 then 1
when 3 then
when a.status in('setup','process') then 1 else 0
end as rolestatus,
case when a.requestortype ='bulkprocess' then 'N/A'
end as requestorno,
case when (fnallowedstate(@adminid) = 1 then 'active' else 'inactive'as state,
from Rules A join ruledetails B on a.ruleid = b.ruleid(this is guid)
where @adminrole in(1,3,2,4)
i want to add more conditions to the where clause but the conditions are based on the calculated columns in the above query. like
where @adminrole in(1,3,2,4) and state = 'active', I created a CTE and added the condition. but that is not being accepted as part of code review. reason being CTEs cannot be optimized and they take lot of memory. The query returns around 300,000 rows with out second condition in where clause.
If i take the result of above query in a temp table or query the function result i can achieve what i need.But this is taking some time which i wanted to eliminate. i wanted to avoid retrieving unwanted rows in the first place. How to go about this without CTE?
Any help is appreciated. Thanks for your time.