cte and where condition

My code runs as below(more columns are there but eliminated for space )
Create function Getresults(@adminID varchar(20),@adminrole int,@condition nvarchar(max))
returns table
Select a.tno,
            case @adminrole
                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' 
                 else a.requestorno 
         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)


Open in new window

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duy PhamFreelance IT ConsultantCommented:
Would it possible to use a sub query instead? Sample pseudo code could be like:
SELECT  <final_result_columns>
    SELECT  <columns>
    FROM Rules
    WHERE  <some_conditions_to_filter_out_unwanted_rows>
) beforeFinalResultSet
WHERE <some_conditions>

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.