Is there a way to have dynamic table headers?

Question

Is there a way to have dynamic table headers?

water490

Ten Centuries

Points: 1148
More actions
September 6, 2025 at 11:57 pm

#4647325
Hi everyone
I have a SP which compares two tables and outputs mismatching entries:
```
SELECT T1.QUOTE_DATE AS REPORT_UNIT_DATE
FROM #UNIT AS T1

SELECT MAX(TRADE_DATE) AS REPORT_DIVISION_DATE
FROM DBO.DIVISION

SELECT T1.UNIT_SYMBOL AS UNIT_TABLE,
T2.DIVISION_SYMBOL AS DIVISION_TABLE
FROM #UNIT AS T1 FULL JOIN #DIVISION AS T2 ON T1.UNIT_SYMBOL = T2.DIVISION_SYMBOL
WHERE T2.DIVISION_SYMBOL IS NULL OR T1.UNIT_SYMBOL IS NULL
```
This is fine if I just run the SP and I get my report. However, I need to use the output for another SP. I could copy the logic from above and put it into the new SP but the problem is that now I have two places in my DB that have identical code so it is not ideal from a maintenance perspective. I have to remember if logic is changed in one SP then the other related SP has to also be updated. I may not remember to do this each time.
I would like to output the SP to a physical table. The challenge is that I have 3 SELECT statements so I don't know how to combine them. The SP is using data from two different tables so I need to know the "age" of the data in the tables. This information comes from the first 2 SELECT queries. For example, the first SELECT may return "2025-08-29" and the second SELECT may return "2025-09-01". Ideally, I would like to update the third SELECT query so the following is returned
UNIT_TABLE_2025_08_29
DIVISION_TABLE_2025_09_01
Each time the SP is run the above two headers would be updated with new dates.
Is this possible? If yes, how can I do it?
Thank you
September 26, 2025 at 2:28 pm

This was removed by the editor as SPAM
September 26, 2025 at 2:33 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24968 More actions · Answer 1

My opinion - The first and second query are not going to be changing much, if at all as there is no logic in them, so duplicating them across 2 stored procedures feels like low risk to me. The third feels like it could be replaced with a view, then just select from the view in both stored procedures. So if changes are needed in the third, it is reflected across both SP's due to it being in a view.

ALTERNATELY, you could break the 3 queries into their own stored procedures then you have 4th one that calls the other 3 in order and you can then reuse them. Pros to this are that you are reusing code which is always nice. Cons are that if there is a bug, you are now digging through multiple layers of stored procedures to try to find and fix it. Another pro is that the SP's are short and to the point, so debugging and doing covering tests on each of them is easy to do, even easier since none of them have parameters - the SP likely has no parameters either, so it is trivial to test and debug it.

Now as for the "header" part of the question, I don't understand what you mean. As far as I am aware, tables don't have headers - they have columns. BUT what I think you mean is that you want the column alias to be UNIT_TABLE_<date> and the only way you are going to manage that is with dynamic SQL which I encourage against as it is a potential for SQL injection. Now, in your specific scenario, I see little risk in using dynamic SQL in that third query as there are no parameters. There may be other potentially better ways to do it, but to me dynamic SQL seems like the easiest way to do it.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

water490 Ten Centuries Points: 1148 More actions · Answer 2

Thank you for the reply.

Yes I mean column titles. That is what I mean by "headers". Sorry for the confusion. Looks like dynamic SQL is a possibility. No clue how to do it tho. Do you have any suggestions?

Mr. Brian Gale SSC-Insane Points: 24968 More actions · Answer 3

Lots of resources on dynamic SQL, but the basics of it are that you are storing your SQL TEXT in an NVARCHAR variable and then passing it to the stored procedure sp_executeSQL. Quick search gave me this code sample:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT * FROM production.products';
EXEC sp_executesql @sql;

So you just need to build that out to suit your needs.

It MAY (likely is) be better though to handle column name changes at the application layer rather than the SQL layer mind you, but that's really your call. Depends on your use case, but I have yet to find a good use case where column aliases can be changed and not break the application layer and where it isn't easier to modify it at the application layer.

Make sure you read this before you do anything with Dynamic SQL though:

https://www.sommarskog.se/dynamic_sql.html

LOTS to read through there but it is a very good resource for dynamic SQL and the various ways to run it as well as the pitfalls.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.