[AccessD] Feeding parameters to SQL Server

John,

Convert the SQL server view to a SQL server function that returns a table.
Functions take parameters and return data sets based on the parameters that
were passed to it.


The general syntax for such a function would be...


CREATE FUNCTION dbo.MyFunction
(
	@Parameter1	SQLDataType.
	@Parameter2	SQLDataType
)
RETURNS TABLE
AS
RETURN
(
SELECT
	fieldName1,
	fieldName2
FROM MyTable a (nolock)
WHERE a.whereField1 BETWEEN @Parameter1 AND @Parameter2
 )



Eric 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Wednesday, February 21, 2007 9:59 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Feeding parameters to SQL Server

Is it possible to feed a parameter to a SQL Server view from Access?  I am
linking (ODBC) to a pair of tables in SQL Server.  The views return a set of
data which will only grow larger over time.  I would like to somehow tell
sql server where... and feed in the value - where checkdate >=X and <=Y for
example.  These views are then used in queries inside of Access, linked to
my tables and stuff.
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.441 / Virus Database: 268.18.3/694 - Release Date: 2/20/2007
1:44 PM