Selecting fields between 2 tables

Hello All,

I am unsure how to approach this complicated dilemma
I have 2 tables that I constructed through sub selects. I want to pull all the fields in one table if the user input is a specific number and ignore the other table fields, and vice versa for the other table. Can this be done and if so how?

so far I have tried With statements, Unions I know will not work, and integrating the one query into the other won't make sense in the way that it is built...unless someone else can clarify.

Thanks in advance for any suggestions.

*Portion of the code
bellow is similar to the other table it contains a derived table with ask for a link, but with a different CF.ID
(
SELECT CVL.INFO AS BOOKCOLLECTIONLINK,
CVL.RECORDID
FROM CUSTOMFIELD CF
INNER JOIN CUSTOMVARCHARLONG CVL ON
CVL.CUSTOMFIELDID = CF.ID
WHERE CF.ID = 36 --Field I want to make into a parameter to specify which table to use
AND CVL.INFO = ::LINK --User Input
) AS LINKPART ON

This doesn't seem to be complicsted with an SP, but maybe you don't even need SP just a correct WHERE condition.

But could you be more specific or give a real world example? So i'll correctly understand what you want.