Pull NULL if column not present in table while UNION SQL Server

Question

I am currently building a dynamic SQL query. The tables and columns are sent as parameters. So the columns may not be present in the table. Is there a way to pull NULL data in the result set when the column is not present in the table?

ex:

SELECT * FROM Table1

Output:

created date | Name  | Salary | Married
-------------+-------+--------+----------
25-Jan-2016  | Chris |  2500  | Y
27-Jan-2016  | John  |  4576  | N
30-Jan-2016  | June  |  3401  | N

So when I run the query below

SELECT Created_date, Name, Age, Married 
FROM Table1

I need to get

created date | Name  |  AGE   | Married
-------------+-------+--------+----------
25-Jan-2016  | Chris |  NULL  | Y
27-Jan-2016  | John  |  NULL  | N
30-Jan-2016  | June  |  NULL  | N

Does anything like IF NOT EXISTS or ISNULL work in this?

I can't use extensive T-SQL in this segment and need to be simple since I am creating a UNION query to more than 50 tables (requirement :| ) . Any advice would be of great help to me.

Answer 1

I can't think of an easy solution. Since you're using dynamic sql, instead of

(previous dynamic string part)+' fieldname '+(next dynamic string part)

you could use

(previous dynamic string part)
+ case when exists (
select 1 
from sys.tables t 
inner join sys.columns c on t.object_id=c.object_id
where c.name=your_field_name and t.name=your_table_name)
) then ' fieldname ' else ' NULL ' end
+(next dynamic string part)