September 6, 2025 at 11:57 pm
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 7, 2025 at 2:19 am
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.
September 7, 2025 at 2:51 am
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?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply