Eric Barro
ebarro at verizon.net
Wed Feb 21 12:53:06 CST 2007
Sorry...the general syntax for using the function is SELECT * FROM dbo.MyFunction(param1, param2) This will return the correct dataset You can use it as a regular "table" in a query this way... SELECT field1, field2 FROM myAccessTable a INNER JOIN ( SELECT * FROM dbo.MyFunction(param1, param2) ) b ON a.accessField = b.functionField -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Wednesday, February 21, 2007 10:37 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Feeding parameters to SQL Server Eric, What is responsible for feeding in the parameters? Can the query that uses the linked view do that? John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro Sent: Wednesday, February 21, 2007 1:05 PM To: 'Access Developers discussion and problem solving' Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.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