[AccessD] Feeding parameters to SQL Server

Hello John,

Let's suppose you use NorthWind sample database and you have to calculate
total Freight for a Shipper given from/to dates.

I assume you do not use ADPs - if you have used them then you could have
created

- a stored procedure:
=====================

CREATE PROCEDURE usp_TotalFreightStats (@fromDate datetime, @toDate datetime
)
AS
BEGIN
	select ShipName, Sum(Freight) as FreightTotal
	from Orders
	where Orders.OrderDate between @fromDate and @toDate
	Group by ShipName
END

- or a UDF
===========
CREATE FUNCTION TotalFreightStats (@fromDate datetime, @toDate datetime )
RETURNS @TotalFreightStatsTable TABLE
   (
    ShipperName   nvarchar(80),
    TotalFreight       money
   )
AS
BEGIN
   declare @fromDate1 dateTime
   declare @toDate1 dateTime
   INSERT @TotalFreightStatsTable
	select ShipName, Sum(Freight) as FreightTotal
	from Orders
	where Orders.OrderDate between @fromDate and @toDate
	Group by ShipName
   RETURN
END

And you could have bound your report to this stored procedure or UDF - on
open this report would have requested from/to dates in parameters input
dialog.

I assume you use mdb and then you can:

- create table to keep parameters
==================================

CREATE TABLE [dbo].[ParametersForUDFs] (
	[fromDate] [datetime] NULL ,
	[toDate] [datetime] NULL 
) ON [PRIMARY]
GO

- populate this table with parameters' values (clear in first)
=============================================================

delete from ParametersForUDFs
insert into ParametersForUDFs (fromDate, toDate)
 values ('1996-07-04', '1996-07-12')

- create UDF
============

CREATE FUNCTION TotalFreightStats1 ( )
RETURNS @TotalFreightStatsTable TABLE
   (
    ShipperName   nvarchar(80),
    TotalFreight       money
   )
AS
BEGIN
   INSERT @TotalFreightStatsTable
	select ShipName, Sum(Freight) as FreightTotal
	from Orders
	where Orders.OrderDate 
                  between (select top 1 fromDate from ParametersForUDFs) 
                  and (select top 1 toDate from ParametersForUDFs) 
	Group by ShipName
   RETURN
END

- use UDF in view
==================

CREATE VIEW dbo.vw_TotalFreightStats1
AS
SELECT     ShipperName, TotalFreight
FROM         dbo.TotalFreightStats1() TotalFreightStats1

++++++++++++++++++++++++++++++++++++
- link view to MS Access mdb
++++++++++++++++++++++++++++++++++++

Of course that approach will not work well in multi-user environment if
different users will need different from/to dates...


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

Well... I dunno.

I have a view in SQL Server returning 8 fields, 6K records (currently),
completely unfiltered, all records in the table.  I pull that linked table
into a query in order to do some preliminary filtering - check date between
a pair of dates and check number not null.

I want the view in sql server to do the "check number not null" for me, and
I know how to do that.

I want the SQL Server to take a pair of parameters - "From Date" and "To
Date" and return only those records.

If I could do this, then I could get rid of my "base query" in access which
applies these filters, reduce traffic across the wire etc.  However even if
I do this, the view is not being requested by a form in XP (for example)
where some magical thing happens in Access to pass the parameters off to SQL
Server.  The linked view will be used immediately in another query which
joins this view to other tables (on one field - BEID - in the view) in order
to build up a set of data for a report.

So the "parent" query that is using the view will have to "feed" the
parameters to the linked view.  Being a nubee to SQL Server I do not even
know if what I am attempting to do is possible.  I cannot wrap my mind
around how a query that is using a view can know that the view needs
parameters, and if it did, what to feed it.  If it is possible, I have never
done so obviously, so I don't even have the basics of what to set in Access
to do the parameter passing.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

<<< tail skipped >>>