1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rathan Gopi
  • 25
  • 4

1 Answers1

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)
George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • 1
    I can not do this for all the columns for all the tables :| .. On an average there are 10 columns sent as params.. – Rathan Gopi Jun 22 '18 at 12:04
  • All I can say is...I'm sorry! I don't know any other way. If there is, it'll probably pop up as another answer here shortly. – George Menoutis Jun 22 '18 at 12:10
  • You only need to do this once - inside a loop. Assume you are building your select statement in a string var where you loop over tables and then inner loop over fields. If the field exists append it to select clause. If not append `NULL AS FieldName`. – David Rushton Jun 22 '18 at 12:17
  • nice idea destination-data! – George Menoutis Jun 22 '18 at 12:21
  • @destination-data that is what I am currently planning to do but would be nice if there is a better way to do it rather than joining with sys tables. MS should really think of little issues like this. IF ERROR( SELECT AGE FROM Table1, NULL) would be great. – Rathan Gopi Jun 22 '18 at 12:24
  • you *could* use try-catch logic to do what you are saying, but it seems even more of a headache. To be honest, I wouldn't blame MS for not having something like this - it is an exotic, in my opinion. – George Menoutis Jun 22 '18 at 12:26