Re: Stored procedure with in a Sub Report

Tech-Archive recommends: Fix windows errors by optimizing your registry

The syntaxe for SP with parameters is easy:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

It's always a good idea to put SET NOCOUNT ON at the beginning of stored
procedures (SP) that will be used with ADP because SP with multi-select
statements won't always be compatible with ADP otherwise. The above example
is not a multi-selects SP but it doesn't hurt to always put it there so you
won't forget it later if you ever need it (ie. if the SP ever become more
complexe). If you don't know what you are doing, it would also be a good
idea to either add the WITH RECOMPILE option or to use intermediary
variables in order to eliminate parameters sniffing from SQL-Server; which
is something that could seriously hurts performance:

Create Procedure dbo.MySP (@IdKey int) WITH RECOMPILE
AS
Set NoCount ON
Select * from MyTable where MyTable.IdKey = @IdKey
GO

or:

Create Procedure dbo.MySP (@IdKey int)
AS
Set NoCount ON

Declare @IdKey2 int
Set @IdKey2 = @IdKey

Select * from MyTable where MyTable.IdKey = @IdKey2
GO

You don't need to use both.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"ToniS" <ToniS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C2BB739-4880-4EBC-9DD9-9F8FC8BE180B@xxxxxxxxxxxxxxxx
thank you for the quick response. I did what you suggested, and it seems
to
be working for the most part (prints all of the lines for each Exhibitor).
Looks like you are correct about the link child and master fields not
working.. I think I will be able to get around that by passing in a
parmater
in the Stored Procedure? I will rsearch to see what the syntext will be
to
do that.

Thanks again.

"Sylvain Lafontaine" wrote:

Probably because ADP is trying to put a server filter on an EXEC
statement
when the subreport is used as a subreport; not as a main report (as it is
when you try it independantly).

Remove the EXEC word and the paramaters that follow, set the RecordSource
to
the name of the stored procedure, the Record Source Qualifier to dbo and
use
only parameters for the SP of the subreport that are either present as
fields on the RecordSource or as bound controls (name) of the main report
(names and fields without the @ of the parameter(s)).

If I remember correctly, the Link Child and Master Fields are useless
when a
stored procedure is used as the record source of a subreport (they are
only
good when using a table or a view).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"ToniS" <ToniS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F6A44966-839A-4EEB-9A1B-50F5B216FEF8@xxxxxxxxxxxxxxxx
I tested the sub report with the Stored procedure and it works. As soon
as
I
run the main report I get the following error: "Incorrect Syntax near
the
keyword 'Exec' With in the subreport Recordsource I have the following
text
exec [GetRepGroups]. Any ideas on what I need to do?


Thanks
ToniS








.

Relevant Pages

  • Re: Stored procedure with in a Sub Report
    ... The link field for the main and subreport was ExhibitorShowID. ... It's always a good idea to put SET NOCOUNT ON at the beginning of stored ... Create Procedure dbo.MySP (@IdKey int) WITH RECOMPILE ... Sylvain Lafontaine, ing. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Stored procedure with in a Sub Report
    ... Sylvain Lafontaine, ing. ... This value will be passed automatically to the subreport by ... It's always a good idea to put SET NOCOUNT ON at the beginning of stored ... Create Procedure dbo.MySP (@IdKey int) WITH RECOMPILE ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Form using stored procedure does not work in Access Runtime
    ... B> I am using a parameterised stored procedure as a recordsource of a form. ... I would remove SET NOCOUNT ON. ... Vadim Rapp ...
    (microsoft.public.access.adp.sqlserver)