[AccessD] Feeding parameters to SQL Server

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
 




More information about the AccessD mailing list