How to search data from two table in SQL Server

Hi Nedash,

I have created a query which full fill your requirement

SQL

DECLARE @Actor AS TABLE(ID INT,NAME VARCHAR(30),GENRE CHAR(6))
INSERT INTO @Actor
VALUES(1,'BradPitt','Man')
INSERT INTO @Actor
VALUES(2,'Jeniferloopez','WOMAN')

DECLARE @Director AS TABLE(ID INT,NAME VARCHAR(30),GENRE CHAR(6))
INSERT INTO @Director(ID,NAME,GENRE)
VALUES(1,'steven spilberg','Man')

DECLARE @Name VARCHAR(30) 
SET @Name = 'BradPitt'


SELECT ID,NAME,GENRE FROM  @Actor
WHERE NAME LIKE @Name + '%' OR @Name IS NULL
UNION ALL
SELECT ID,NAME,GENRE FROM  @Director
WHERE NAME LIKE @Name + '%' OR @Name IS NULL

Hope this works for you