I am creating a stored procedure that is logging some data. The data ultimately needs to end up in 2 tables. The incoming data is coming from a JSON string containing 15 parameters, and the data is then logged to the database using the stored procedure I'm writing. I would like to send the data into 1 stored procedure and INSERT it into both tables.
The first table is a raw data logging table. It will be used for debug and troubleshooting.
The second table will be used for report generation. This table requires some simple mathematical calculations to be done on incoming data. For example:
DECLARE @Table2Fld3 DECIMAL = @IncomingFld9 - @IncomingFld4;
I'll have about 8 of these calculations to do to compute the values for table 2, then I'll do an INSERT to save the data.
So my question is, is it a good practice to do these calculations in the T-SQL? Or would it be better for me to create 2 separate stored procedures and do the calculations in my code?
One trade-off I see is that if I do it all in the code then I have to create 2 database connections.
I should elaborate on the "2 database connections" comment. The application in question is a windows service that establishes multi-threaded server/client communication. The logging system is asynchronous to the server/client communication. Using that existing system, in order for me to target multiple stored procedures, it would require 2 calls to the logger which would spin up 2 connections to the database.