February 10, 2016 at 2:54 am
I have the case where I need to perform a query over a table that i do not know its name at design time.
For example, I have the below tables:
Table_Items_1
Table_Items_2
Table_Items_3
Table_Items_4
Table_Items_5
I need to select depending on a number "x" which refers to the suffix of the table. (Number of tables is dynamic, I cannot do if-else).
This can be done using dynamic SQL. However, I need to have it more generic, since it will be referenced by several other functions/SPs.
I was thinking of Table Valued Functions, since I need to perform other queries over the above query (i.e.: inner joins, other select queries that depend on the result or fields etc.) but Table valued functions do not accept calling stored procedures nor dynamic SQL.
P.S.: I do not mind changing the method, TVF are not a must, yet, I need something generic that can be references from other stored procedures/functions.
Any solution?
Thank you.
February 10, 2016 at 5:00 am
What you are proposing is a very bad idea. Dynamic SQL in itself is already a security risk, but the risk is limited if you properly parametrize it. However, table names cannot be parametrized, so by using dynamic SQL the way you want to, you open yourself up to what among hackers is still the most popular method.
If you do not know the table names or even the number of tables, yet you apparently do know the columns (else you would be fully unable to write any form of query, right?), then I am 99.9% sure that your database design is flawed. Do yourself, your management, and your successors a favor and get that design improved.
Based on the table names you mention in your question, it appears as if you are trying to implement some form of partitioning. Have you looked into the SQL Server feature "table partitioning"?
(Or, if you do not have Enterprise Edition, look into partitioned views as a very good alternative).
February 10, 2016 at 6:00 am
Thank you for your reply.
True, we are taking into consideration the security risks and we are have minimized the parameters to minimum with several validations. However, the above is somehow needed.
I will check your suggestion however. Thank you again.
February 10, 2016 at 12:01 pm
There's actually a good method to validate table names.
This is a generic example that you need to adapt to your needs. An invalid table name won't do anything.
DECLARE @table_name sysname = 'Tally'; --Your table name.
DECLARE @SQL nvarchar(max);
SELECT @SQL = 'SELECT n FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = @table_name
EXEC (@SQL)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply