Access Reports from SQL Server Stored Procedures

From: Drew (drew.laing_at_NOswvtc.dmhmrsas.virginia.SPMgov)
Date: 12/16/04 

Date: Thu, 16 Dec 2004 09:13:01 -0500

If I set up a Stored Proc in SQL Server, and using an Access ADP, I can
build a report from the Stored Procedure. All this works fine and dandy,
except when I add a paramter to the Stored Procedure. Like for instance,

CREATE PROCEDURE spCheckReceiving
@Shift int
AS
IF @Shift = 1
SELECT TOP 100 PERCENT E.EmpID, E.EmpFName, E.EmpMName, E.EmpLName,
EP.EmpSSN, P.PosTimeKeeper, D.DeptName
FROM dbo.tblEmpInfo EF INNER JOIN
                      EmpCore.dbo.tblEmployee E ON EF.EmpID = E.EmpID INNER
JOIN
                      EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID =
EP.EmpID INNER JOIN
                      EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER
JOIN
                      EmpCore.dbo.tblTimeKeepers TK ON P.PosTimeKeeper =
TK.TimeKeeperID INNER JOIN
                      EmpCore.dbo.tblDept D ON P.PosDeptID = D.DeptID
WHERE (E.EmpActive = 1) AND (EF.EmpDirectDeposit = 0) AND (E.EmpShift =
1 OR E.EmpShift = 2 OR E.EmpShift = 0)
ORDER BY E.EmpLName, E.EmpFName
ELSE
SELECT TOP 100 PERCENT E.EmpID, E.EmpFName, E.EmpMName, E.EmpLName,
EP.EmpSSN, P.PosTimeKeeper, D.DeptName
FROM dbo.tblEmpInfo EF INNER JOIN
                      EmpCore.dbo.tblEmployee E ON EF.EmpID = E.EmpID INNER
JOIN
                      EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID =
EP.EmpID INNER JOIN
                      EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER
JOIN
                      EmpCore.dbo.tblTimeKeepers TK ON P.PosTimeKeeper =
TK.TimeKeeperID INNER JOIN
                      EmpCore.dbo.tblDept D ON P.PosDeptID = D.DeptID
WHERE (E.EmpActive = 1) AND (EF.EmpDirectDeposit = 0) AND (E.EmpShift =
3)
ORDER BY E.EmpLName, E.EmpFName
GO

Now, if I set up a report with this stored procedure and run the report, it
will automatically prompt me for "Shift". Here if I enter 1 then it will
pass this to the SP and use the first query, if I put in anything but a 1
(2,3,etc) then it will run the 2nd query...

How can I get the input from the user on the stored procedure parameter? I
need to do this so that I can change up the grouplevel property if the
second query is used.

Thanks,
Drew Laing

Relevant Pages

  • Strange bug, hard to reproduce - is it known?
    ... tables used in the query, nor the query itself were changed (I did make ... particular stored procedure), yet it did work correctly before. ... tables involved in my test database, using the EXACT same columns, ... INNER JOIN dbo.GR AS r ...
    (microsoft.public.sqlserver.programming)
  • Re: Dynamic Stored Procedure
    ... form or report you can define the parameters in the InputParameters property ... that is based off of stored procedure "SP_Alpha" that requires a parameter ... > INNER JOIN ... > Dim strRecordSource As String ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Strange bug, hard to reproduce - is it known?
    ... > tables used in the query, nor the query itself were changed (I did make ... > particular stored procedure), yet it did work correctly before. ... > tables involved in my test database, using the EXACT same columns, ... > INNER JOIN dbo.GR AS r ...
    (microsoft.public.sqlserver.programming)
  • Re: Query success in Query Analyzer but not in Reporting Services?
    ... the scenes work on the schema and dropping the temp tables messes this up). ... When you did a stored procedure this should work. ... RS will detect the parameters and handle it all for you, creating report ... Query Analyzer as opposed to when it is run in Report Designer. ...
    (microsoft.public.sqlserver.datamining)
  • Ignor previous...Wrong Thread
    ... > form or report you can define the parameters in the InputParameters ... >> Here is my Stored Procedure, ... >> INNER JOIN ... >> Dim strRecordSource As String ...
    (microsoft.public.access.adp.sqlserver)