I have the following query
SELECT IMPORTE_ENE_2021 * PORCENTAJE AS COMISION
FROM TBL_INGRESOS
This query is within a Stored Procedure, the detail that for the following years the TBL_INCOME table is eliminated and generated with a new format, that is, the next year the name of the column Amount_Ene_2021 would be Amount_Ene_2022 and I think that the ideal is not to be modifying the SP every time the year changes.
Is there any way to get the name of the columns and that they are being replaced automatically in this query?
Really handling the data in this way goes against the concept of relational databases, but hey, stranger things have been seen. First of all, I must tell you that your problem is not so much how to get the name of a column, but rather how to use it: any standard SQL query requires that we know in advance the names of the columns that will be used.
There are not many ways to solve this and I would dare to say that they are not the best either.
Fixed structure:
If the table had a fixed structure and as permanent as possible over time, I could suggest you do an insert type
INSERT Tabla SELECT * FROM TBL_INGRESOS
, that is, let's assume for a moment that you have a table like this:The only thing that varies from year to year is the name of the column
IMPORTE_ENE_<año>
, in this case you could define an intermediate temporary table, to "rename" the column to a more generic name:With this we have managed to rename the column in question by the most generic and useful name
IMPORTE_ANIO
. The fragility of this is clear, when a new column is added or eventually the type of any of these is modified, the insertion will failBy doing this, your final query will remain stable:
Dynamic query:
These types of queries allow a lot of flexibility, but the downside is that they do not usually take advantage of any optimizations that the engine could make, and in some cases, although not in this one, they can be an entry point for malicious code injections.
According to what you say, SQL Server has some tables where the fields of the tables can be obtained, we can use that to search for the columns of the tables, for more information I leave you this link https://docs.microsoft. com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-ver15
Now according to what you say for a field, you could do what I am telling you about and obtain the column with a like from which you are looking for an example AMOUNT_ENE_ and it would bring you the match and then you just concatenate it with the following I will put it in a PRINT but that PRINT would be changed by an EXEC so that it executes the desired statement, leaving something like this
---Now as you tell me what would be the months, this occurred to me, only the aliases would have to be seen how to do them since I don't know the functionality, but the idea of this is that it concatenates the fields by the amount and obtains the value of a time.
Any questions you have let me know.
Cheers