How can I do this join

I need to only add this join if the person has a specific role? if dbo.IsInRole(@requestorId, 16) = 0

LEFT JOIN tblactivityorg ao
ON ao.id = p.activityorgid

       if dbo.IsInRole(@requestorId, 16) = 0
       Begin
       JOIN
              (
              SELECT DISTINCT dbo.tblPayrollOrgs.ActivityOrgId
                    FROM         dbo.tblPayrollOrgs INNER JOIN.........
                    WHERE     (dbo.tblRoleCandidates.EmployeeId = @requestorid)
              ) po on po.ActivityOrgId = ao.Id
       LEFT JOIN tbltwpositioneligibility pe 
              ON pe.id = x.twposeligibleid 
       left join (.........
       END

Add that to the join condition, for example,

JOIN
              (
              SELECT DISTINCT dbo.tblPayrollOrgs.ActivityOrgId
                    FROM         dbo.tblPayrollOrgs INNER JOIN.........
                    WHERE     (dbo.tblRoleCandidates.EmployeeId = @requestorid)
              ) po on po.ActivityOrgId = ao.Id AND dbo.IsInRole(@requestorId, 16) = 0

Or, you could even do this:

declare @isInRole BIT;
SET @isInRole = dbo.IsInRole(@requestorId, 16) ;

Then use that variable instead of the function in the join condition. You may want to change the INNER JOIN to LEFT JOIN depending on what you want to accomplish.

I have to keep the JOIN, so the " AND dbo.IsInRole(@requestorId, 16) = 0"
will not work

Change the JOIN to LEFT JOIN. Alternatively, change the column(s) you select to
SELECT DISTINCT CASE WHEN dbo.IsInRole(@requestorId, 16) = 0 THEN dbo.tblPayrollOrgs.ActivityOrgId END as ActivityOrgId .....

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

For a quick education on RDBMS and basic data modeling, read this eight part series: http://www.sqlservercentral.com/stairway/72899/

We do not use bit flags and masks in SQL; that was Assembly language. Is this what our “IsInRole(@requestor_Id, 16) = 0” does?

Why do data element names change from table to table? Why is there a magical generic “id” in the schema?

Putting the silly “tbl-” prefix on table names is called a tibble and we laugh at this design error.

Based on the fragments you posted and 30+ years of cleaning up bad SQL, my guess I is that your schema is disaster that needs to be replaced.

Please follow forum rules and show us the DDL. Then we can try to help you.

Yes, I agree... Even though you have a very nice scientific calculator, you should only use the basic 4 functions because of the mythical seamless compatibility with all other SQL engines even if they aren't ISO compliant themselves and your not existent migration might fail if you do otherwise. :wink:

Dialects have a way of disappearing. Vendors want to support standards, which is where they join committees. One of my favorites was the BIT data type in T-SQL.

At first it was what computer science people think of as a bit {0, 1}, but then Microsoft made it a real data type. In SQL all data types are NULL-able {0, one, null}. This really screwed up a lot of code (and made me a fortune, cleaning up T-SQL databases).

When T-or SQL only had the *= version of outer join, I would write code and insert the ANSI infixed outer join version of the query as a comment. My clients only had to comment out the old code and make my comments live code. Your clients had to call you back, get billed again and get ready for deprecation.

While I understand it "job secure" programming, I really honestly try to avoid it.

Oh heck, it's Christmas! Merry Christmas everyone, and if you don't celebrate Christmas, Happy holidays.

Understood and very much appreciated on the *= thing but that's an exception rather than the rule. Would you suggest that no one use the likes of (for example) "Hekaton" just because it is proprietary? (I have other reasons for recommending that people don't use it but being proprietary isn't one of them).

Same to you, James! Merry Christmas and Happy Holidays everyone!

Actually, in the early days of SQL, the Extended Equality outer join (*=, +=, =, etc) was the rule, not the exception. The difference in it was Oracle did it one way, Sybase did it the opposite way and Gupta (later Centura) let you pick either the Sybase or Oracle method at configuration time. Then Informix had a completely different syntax done in the from clause, to show the preserved and unpreserved tables.

Likewise, ISNULL(), NVL() and some other early functions all behaved a little differently from COALESCE(). The coalesce function looks down the list of options, and finds the highest data type. This becomes the data type of the expression. ISNULL uses the first data type of the first parameter. There was also something weird about NVL that I cannot remember (I do not work that much with Oracle).

The original Sybase T SQL believed in existential import. This was a debate in the early days of mathematical logic, which asserted that "all men are mortal" Implies that at least one man exists. Lewis Carroll held this position, but modern logic does not.

In one edition of SQL for Smarties, I published a lookup table of differences in the math in various SQL products. The IEEE rules for floating-point math were not in place. Nor did things like division by zero versus division by null exist in the standard.

As I said, this is how I made a living. I knew why and where the lack of standards would prevent two different SQL databases from returning the same result. It is very embarrassing when the front end in SQL Server does not agree with your DB2 backend. The myth I see is the belief that* a company of any size will deal with one and only one SQL product*. Multiple databases (often for different purposes like your in memory example) might be in-house, or at a supplier or an external data source.

As Jerry Weinberg showed in his research (Psychology of Programming) decades ago, If you start with the goal (portability, performance, whatever), your code will hit that goal. He also showed that it will hit the goal very often at the expense of all other considerations (AARGH!).

To your point about companies dealing with more than one database engine... we have SQL Server and DB2. But, the two engines are used for totally different purposes and there's almost no chance that the code from one will ever be needed to be ported to the other especially since their very backbones are so totally different. In the two chance occasions where we needed to port some simple code, it was still impossible to do without a rewrite because SQL <> SQL.

I agree that in the early days, "Extended Equality" was indeed the rule rather than the exception. That was a score of years ago and doesn't make a good example anymore but, as you point out, even that was different between Oracle and SQL Server/Sybase. No chance of being 100% portable there unless you wrote things like WHERE IN or WHERE EXISTS.

On the COALESCE thing, it's a great tool but I've seen a lot of people get into real trouble with it because of the data type escalation you speak of. I'll also agree that's just a matter of educating the folks that use it. I just can't spell it before my second cup of coffee in the morning. :wink:

I'm with "modern logic" which, ironically, is very old if you look at the practical side of the "old things". Just because a saying such as "all men are mortal" exists, it doesn't necessarily mean than any men exist. It's a bit like say "all flamgismals are mortal" (apologies if that turns out to be something bad in another language but Google had nothing on the word). It would be the truth only if at least one flamgismal existed and it were mortal and the implication that one may exist would need additional evidence before being declared as a truth, implications be damned. Implications are very much like defaults and innuendo... I don't trust them and so try not to rely on them. The scientific method just doesn't allow for implications except, possibly, as a hypothesis to be proven or disproven or be declared as un-provable either way due to the lack of additional evidence.