Performance of IN with OR operator I have common clause in most of the procedures like

Select * from TABLE A  + Joins  where &lt;Conditions&gt;

And 
(
     -- All Broker
       ('True' = (Select AllBrokers from SiteUser where ID = @SiteUserID)) 
  OR 
       ( 
         A.BrokerID in 
                     (
                       Select BrokerID from SiteUserBroker where SiteUserID 
                      = @SiteUserID)
       )
)


Basically, if the user has access to all brokers the whole filter should not be applied else it should get the list of brokers.

I am a bit worried about the performance as this is used in lot of procedures. Data has started reaching over 100,000 records and will grow soon, so can this be better written?

Any ideas are appreciated.
You are correct that it is not a good practice to bundle the permission check in the relational query. The extra predicates can make the optimizer's choice more complicated, and the code is not very readable. It also may cause the optimizer to process all the data from the actual SELECT you are looking for, even if the permission check ends up failing, and returning an empty set.

What if instead, you split it into 2 phases - first check for the permissions, and then execute your query only if permissions check succeeds.
For example:

IF (
    -- All Broker
    ( 'True' = (
                SELECT AllBrokers 
                FROM   SiteUser 
                WHERE  ID = @SiteUserID
                )
    ) 
    OR 
    ( A.BrokerID IN (
                     SELECT BrokerID 
                     FROM   SiteUserBroker 
                     WHERE  SiteUserID = @SiteUserID
                    )
    )
   )
SELECT * 
FROM TABLE A  &lt;+ Joins&gt;  
WHERE &lt;Conditions&gt;
ELSE
SELECT NULL AS ColumnA, NULL AS ColumnB... -- all columns as the * above
WHERE 1=0 -- Forces an empty set


This will keep the same output columns of the set either if permission fail, or succeed, but will skip the actual SELECT processing if it fails. It is also much more readable IMHO.

An even better approach would be to raise an explicit access denied message, for example:

ELSE
THROW &lt;Access Denied Error&gt;


However, this approach may require application code changes to handle the message correctly.

BTW - never use SELECT * in production code, see this thread...

HTH