05-24-08, 16:46 #1Registered User
- Join Date
- May 2008
Unanswered: Passing table name as parameter to function
I'm writing reports in Rep. Services that reads data from Dynamics NAV (Navision). In NAV data are stored by company and this is implemented by using the company name as prefix to the table name. This means that in a NAV database with three companies (lets call these companies A, B and C) we will have three tables with customers. The table names will be A$Customer, B$Customer and C$Customer.
Now to my problem:
I wan't to write one report where I can choose company. I do not want to use a stored procedure. I want to use a function so I can use the function in select statements and join several functions to build up a report that needs data from several tables.
Is there some way to pass the table name or a part of the table name to a function that returns the content of the actual table? I know I can pass parameters that I can use in the where clause, but is it possible to do it with the table name. Or is there any other way to solve this.
All ideas are welcome. Thanks.
05-25-08, 10:42 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
yes this is possible using dynamic sql and sp_executesql, BUT what you have described is typically a really bad database design for many reasons including but not limited to maintenance, performance, security (having to use dynamic sql), having to kludge around common programming programs like this etc.....If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.