I am working in an Access 2003 Data Project accessing a SQL Server 2000 database, and I am looking for a simple way to parameterize the queries that I use as the recordsources for my reports and forms. I posted this question here because all questions are about SQL only.Our accounting software has 3 (one for each currency) identically structured databases (Data_05, Data_06, Data_09) on a local server. All of my additional work is being stored in a new database (NEW_DB) i.e. views, functions, stored procedures, and additional tables. I only need read-only access to data in the accounting databases, but I will be adding several additional tables to my NEW_DB database to track additional entities that the accounting software does not allow.I don't want to use dynamic SQL and I don't want to create 3 copies of every query (which could then be selected in VB). There are 8-10 tables in each of the 3 databases that I need to access. To select the OEORDHDR table from one of the 3 databases, I would create one of the two below options in my NEW_DB database.An Inline Table-Defined FunctionALTER FUNCTION .fnOEORDHDR(@Company char(7)) --@Company is 'Data_05', 'Data_06', or 'Data_09'RETURNS TABLEASRETURN( SELECT * FROM DATA_05.dbo.OEORDHDR_SQL WHERE 'Data_05' = @Company UNION ALL SELECT * FROM DATA_06.dbo.OEORDHDR_SQL WHERE 'DATA_06' = @Company UNION ALL SELECT * FROM DATA_09.dbo.OEORDHDR_SQL WHERE 'DATA_09' = @Company )
A Stored ProcedureALTER PROCEDURE MJC.spOEORDHDR(@Company char(7)) --@Company is 'Data_05', 'Data_06', or 'Data_09'AS IF @Company = 'Data_05'SELECT *FROM Data_05.dbo.OEORDHDR_SQLIf @Company = 'Data_06'SELECT *FROM Data_06.dbo.OEORDHDR_SQLIf @Company = 'Data_09'SELECT *FROM Data_09.dbo.OEORDHDR_SQLEndIf
The both essentially do the same thing, I pass them a parameter 'Data_05', 'Data_06', or 'Data_09', and they return the table from the desired database. Performance-wise (the SQL Query Engine is damned impressive) both of these queries perform equally well as simply running 'Select * From Data_0?.dbo.OEORDHDR_SQL', which is nice. However, there are a few key issues/problems I need to deal with: 1. Only functions (not stored procedures) can be used in place of a table in the FROM clause in other queries. The whole reason I am parameterizing the tables is so I can parameterize the views and functions that I create using these tables, so this is a requirement.2. UNION queries are not updatable (I don't have access to alter the database structure of the accounting databases to meet the requirements of creating a partitioned view).For reporting on the accounting data, the first option,the function, works perfectly because I can then create views and functinos off of it and I don't need it to be updatable, case closed (although any suggestions are always appreciated).However, I also mentioned that I will be adding tables to my NEW_DB that I would also like to parametize. For that, I will need the ability to create updatable queries. Is there any way to add IF or CASE logic to a table-valued function, and would it still be updatable? as in this fantasy query of mine that doesn't work:ALTER FUNCTION .fnOEORDHDR(@Company char(7)) --@Company is 'Data_05', 'Data_06', or 'Data_09'RETURNS TABLEASRETURN( IF @Company = 'Data_05'SELECT *FROM Data_05.dbo.OEORDHDR_SQLIf @Company = 'Data_06'SELECT *FROM Data_06.dbo.OEORDHDR_SQLIf @Company = 'Data_09'SELECT *FROM Data_09.dbo.OEORDHDR_SQLEndIf )
Any help would be greatly appreciated, I figured I'd spend the time trying to figure this all out ahead of time before I start developing my application so that I don't have to change everything later.Thank you!
Updateable Table-Defined or Inline Function?
Posted - 2004-03-16 : 12:59:41