Sub select display value if nothing found

Transact-SQL
1

i am trying to figure out how i only get the data for ORIG_HIRE_DT if it exists in the
select from business_rules table and displays i.e 2020-05-05 00:00:00 and if not find it displays
from the table EMPL i.e FIRST_NAME as there is nothing in the BUSINESS_RULES for this entry
select @ORIG_HIRE_DATE , @FIRST_NAME FROM EMPL

---Here i get 2020-01-01 00:00:00 and the replace value 2020-05-05 00:00:00

CREATE TABLE [dbo].[PLAY_A](
[EMPL_ID] nvarchar NULL,
[ORIG_HIRE_DT] [smalldatetime] NULL,
[FIRST_NAME] nvarchar NULL,
) ON [PRIMARY]
GO

INSERT INTO EMPL
SELECT 'TR123' ,'2020-01-01 00:00:00','John'

CREATE TABLE [dbo].[BUSINESS_RULES](
[TableName] nvarchar NULL,
[ColumnName] nvarchar NULL,
[ColumnReplaceValue] nvarchar NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO BUSINESS_RULES
SELECT 'EMPL','ORIG_HIRE_DT','2020-05-05 00:00:00'

---Here i get 2020-01-01 00:00:00 and the replace value 2020-05-05 00:00:00
SELECT EMPL_ID, ORIG_HIRE_DT,
(SELECT
ColumnReplaceValue FROM BUSINESS_RULES WHERE TABLENAME = 'EMPL' AND ColumnName = 'ORIG_HIRE_DT'),
FIRST_NAME,
(SELECT
ColumnReplaceValue FROM BUSINESS_RULES WHERE TABLENAME = 'EMPL' AND ColumnName = 'FIRST_NAME')

	 FROM EMPL

Looking for
|EMPL_ID|ORIG_HIRE_DT|FIRST_NAME
|TR123|2020-05-05 00:00:00|JOHN

2

Your DDL doesn't work and I'm not sure what the PKs are on each table.

CREATE TABLE #PLAY_A(
[EMPL_ID] nvarchar(20) NULL,
[ORIG_HIRE_DT] [smalldatetime] NULL,
[FIRST_NAME] nvarchar (20) NULL)
GO

INSERT INTO #PLAY_A
SELECT 'TR123' ,'2020-01-01 00:00:00','John'

CREATE TABLE #BUSINESS_RULES(
[TableName] nvarchar (20) NULL,
[ColumnName] nvarchar (20) NULL,
[ColumnReplaceValue] nvarchar (20) NULL
) 
GO

INSERT INTO #BUSINESS_RULES
SELECT 'EMPL','ORIG_HIRE_DT','2020-05-05 00:00:00'

select A.Empl_id,
IsNull(CAST(b.ColumnReplaceValue AS SmallDAteTime),a.ORIG_HIRE_DT ) as OriginalHireDate,
a.First_Name
FROM #Play_A as A
left join #BUSINESS_RULES as B
on b.TableName = 'EMPL'
and b.ColumnName = 'ORIG_HIRE_DT'

3

Thanks for the advice.