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 Function
Code:
ALTER FUNCTION .fnOEORDHDR
(@Company char(7)) --@Company is 'Data_05', 'Data_06', or 'Data_09'
RETURNS TABLE
AS
RETURN( 
	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 Procedure
Code:
ALTER   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_SQL
If @Company = 'Data_06'
SELECT *
FROM		Data_06.dbo.OEORDHDR_SQL
If @Company = 'Data_09'
SELECT *
FROM 	Data_09.dbo.OEORDHDR_SQL
EndIf
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:

Code:
ALTER FUNCTION .fnOEORDHDR
(@Company char(7)) --@Company is 'Data_05', 'Data_06', or 'Data_09'
RETURNS TABLE
AS
RETURN( 
IF @Company = 'Data_05'
SELECT     *
FROM       Data_05.dbo.OEORDHDR_SQL
If @Company = 'Data_06'
SELECT *
FROM		Data_06.dbo.OEORDHDR_SQL
If @Company = 'Data_09'
SELECT *
FROM 	Data_09.dbo.OEORDHDR_SQL
EndIf
	)
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!