Conditional column name in SQL where clause-CodePudding

How should the below be handled in SQL? What would be the where clause?

select *
from tbl_A a
inner join tbl_B b on a.pid = b.pid
where #name# like '%@searchText%

But this column #name# is based on the condition - (if pid is null then use a.pname column else use b.name)

CodePudding user response：

You just use regular AND/OR logic...

select *
from tbl_A a
left join tbl_B b on a.pid = b.pid
where (a.pid is null and a.pname like '%'   @SearchText   '%')
or (a.pid is not null and b.pname like '%'   @SearchText   '%');

You'll be wanting to split the search text out as shown to add the wildcards.
You'll be wanting a left join is pid can be null i.e. there is no match.