Stored Procedure Results differ in ISQL/W and Crystal Reports

[Martin Topping]

We're having problems with a number of stored procedures we have written (we're all v. new to SQL Server). The typical scenario is :

sp executes spA,spB and spC
Each uses a cursor, spA,spB and spC out values that sp wants to output with some additional info.

In ISQL/W the output is fine. When the sp is executed from a Crystal Report we always get a single row of data (the 1st). We get the same problem if it is executed from the Access Upsizing Tool's SQL Server Browser utility.

We use SET NOCOUNT ON in sp (not in the others).
If we remove SET NOCOUNT ON we get now rows in Crystal.

I enclose an example (with 3 sub- SPs). Help. Martin


CREATE PROCEDURE coral.qryPayEmp8_newtmp @lQryCompanyNumber int,@dtQryPeriodFromDate datetime,@dtQryPeriodEndDate datetime,@dtQryPayrollYearStartDate datetime,@szQryCompanyTaxReference varchar(30) AS
--variables used for cursors
DECLARE
@lCCUniqueID INT,
@lUniqueID INT,
@lEmployeeNumber INT,
@szEmployeeSurname VARCHAR (20),
@szInitials VARCHAR (4),
@szDeptNo VARCHAR (6),
@dtDateLeft DATETIME,
@EdTotalGrossPayThis FLOAT,
@EdTaxableGrossPayThis FLOAT,
@EdTotalGrossPayPrevious FLOAT,
@EdTaxableGrossPayPrevious FLOAT,
@EdTaxPaidThis FLOAT,
@EdTaxPaidPrevious FLOAT,
@EdSMPPaidToDate FLOAT,
@EdSSPPaidToDate FLOAT,
@PdTotalGrossPayThis FLOAT,
@PdTaxableGrossPayThis FLOAT,
@PdTaxPaidThis FLOAT,
@PdSMPPaid FLOAT,
@PdSSPPaid FLOAT,
@szNICategory VARCHAR (1),

--output from qryEmployeesNIbyCat
@SumOfdNIablePay FLOAT,
@SumOfdEmployersNI FLOAT,
@SumOfdEmployeeNI FLOAT,
@SumOfdContractedOutEarnings FLOAT,
@SumOfdEmployeeNIContractedOut FLOAT,

--output from qryEmployeeNICHol
@SumOfdEmployersNICHoliday FLOAT

SET NOCOUNT ON

DECLARE employee_period CURSOR
FOR
SELECT tblEmployees.lUniqueID,
tblEmployees.lEmployeeNumber,
tblEmployees.szEmployeeSurname,
tblEmployees.szInitials,
tblEmployees.szDeptNo,
tblEmployees.dtDateLeft,
tblEmployees.dTotalGrossPayThis,
tblEmployees.dTaxableGrossPayThis,
tblEmployees.dTotalGrossPayPrevious,
tblEmployees.dTaxableGrossPayPrevious,
tblEmployees.dTaxPaidThis AS EdTaxPaidThis,
tblEmployees.dTaxPaidPrevious,
tblEmployees.dSMPPaidToDate,
tblEmployees.dSSPPaidToDate
FROM tblEmployees
WHERE (tblEmployees.lCompanyNumber = @lQryCompanyNumber)


DECLARE employee_ni_categories SCROLL CURSOR
FOR
SELECT DISTINCT
tblEmployeePeriodDetails.lUniqueID,
tblEmployeePeriodDetails.cNICategory
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))

OPEN employee_period

FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate

WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8b @lUniqueID,
@dtQryPayrollYearStartDate,
@SumOfdEmployersNICHoliday OUTPUT
EXEC qryPayEmp8c @lUniqueID,
@dtQryPeriodEndDate,
@PdTotalGrossPayThis OUTPUT,
@PdTaxableGrossPayThis OUTPUT,
@PdTaxPaidThis OUTPUT,
@PdSMPPaid OUTPUT,
@PdSSPPaid OUTPUT
OPEN employee_ni_categories
FETCH FIRST FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
IF (@@FETCH_STATUS <> -1)
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8a @lUniqueID,
@szNICategory,
@dtQryPayrollYearStartDate,
@SumOfdNIablePay OUTPUT,
@SumOfdEmployersNI OUTPUT,
@SumOfdEmployeeNI OUTPUT,
@SumOfdContractedOutEarnings OUTPUT,
@SumOfdEmployeeNIContractedOut OUTPUT

SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid,
@szNICategory AS szNICategory,
@SumOfdNIablePay AS SumOfdNIablePay,
@SumOfdEmployersNI AS SumOfdEmployersNI,
@SumOfdEmployeeNI AS SumOfdEmployeeNI,
@SumOfdContractedOutEarnings AS SumOfdContractedOutEarnings,
@SumOfdEmployeeNIContractedOut AS SumOfdEmployeeNIContractedOut

FETCH NEXT FROM employee_ni_categories INTO
@lCCUniqueID,
@szNICategory
END
ELSE
SELECT @lUniqueID AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
@szEmployeeSurname AS szSurname,
@szInitials AS szInitials,
@szDeptNo AS szDeptNo,
@dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS EdTaxPaidThis,
@EdTaxPaidPrevious AS EdTaxPaidPrevious,
@EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS PdTaxableGrossPayThis,
@PdTaxPaidThis AS PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,
@PdSSPPaid AS PdSSPPaid
CLOSE employee_ni_categories
FETCH NEXT FROM employee_period INTO
@lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
@szInitials,
@szDeptNo,
@dtDateLeft,
@EdTotalGrossPayThis,
@EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,
@EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
@EdSSPPaidToDate
END

CLOSE employee_period
DEALLOCATE employee_period

DEALLOCATE employee_ni_categories
RETURN

-----------------


CREATE PROCEDURE coral.qryPayEmp8a
@lUniqueID INT = 1,
@szNICategory VARCHAR (1) = &#39;A&#39;,
@dtPayrollYearStartDate DATETIME = &#39;1900-01-01 00:00:00.000&#39;,
@SumOfdNIablePay FLOAT OUTPUT,
@SumOfdEmployersNI FLOAT OUTPUT,
@SumOfdEmployeeNI FLOAT OUTPUT,
@SumOfdContractedOutEarnings FLOAT OUTPUT,
@SumOfdEmployeeNIContractedOut FLOAT OUTPUT
AS
SELECT DISTINCT
@SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay),
@SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI),
@SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI),
@SumOfdContractedOutEarnings = Sum(tblEmployeePeriodDetails.dContractedOutEarning s),
@SumOfdEmployeeNIContractedOut = Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND
(tblEmployeePeriodDetails.cNICategory = @szNICategory) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtPayrollYearStartDate))
GROUP BY tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory

RETURN

-----------------------------

REATE PROCEDURE coral.qryPayEmp8b
@lQryUniqueID INT = 1,
@dtQryPayrollYearStartDate DATETIME = &#39;1900-01-01 00:00:00.000&#39;,
@SumOfdEmployersNICHoliday FLOAT OUTPUT
AS
SELECT
@SumOfdEmployersNICHoliday = Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday)
FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))


RETURN

--------------------

CREATE PROCEDURE coral.qryPayEmp8c
@lQryUniqueID INT = 1,
@dtQryPeriodEndDate DATETIME = &#39;1900-01-01 00:00:00.000&#39;,
@PdTaxableGrossPayThis FLOAT OUTPUT,
@PdTaxPaidThis FLOAT OUTPUT,
@PdSMPPaid FLOAT OUTPUT,
@PdTotalGrossPayThis FLOAT OUTPUT,
@PdSSPPaid FLOAT OUTPUT
AS
SET NOCOUNT ON
SELECT @PdTotalGrossPayThis = tblEmployeePeriodDetails.dTotalGrossPayThis,
@PdTaxableGrossPayThis = tblEmployeePeriodDetails.dTaxableGrossPayThis ,
@PdTaxPaidThis = tblEmployeePeriodDetails.dTaxPaidThis,
@PdSMPPaid = tblEmployeePeriodDetails.dSMPPaid,
@PdSSPPaid = tblEmployeePeriodDetails.dSSPPaid

FROM tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate = @dtQryPeriodEndDate))


RETURN

[Peri]

Hi Martin,
Crystal Reports treats each select as one result set and it may not processes multiple result sets. Insert all
records to a temp table and at the end select everything from temp table.

Peri



On 10/29/98 5:20:42 AM, Martin Topping wrote:
> We&#39;re having problems with a number of stored procedures we have
> written (we&#39;re all v. new to SQL Server). The typical scenario is
> :

sp executes spA,spB and spC
Each uses a cursor, spA,spB and spC out
> values that sp wants to output with some additional info.

In ISQL/W the
> output is fine. When the sp is executed from a Crystal Report we always get
> a single row of data (the 1st). We get the same problem if it is executed
> from the Access Upsizing Tool&#39;s SQL Server Browser utility.

We use
> SET NOCOUNT ON in sp (not in the others).
If we remove SET NOCOUNT ON we
> get now rows in Crystal.

I enclose an example (with 3 sub- SPs). Help.
> Martin


CREATE PROCEDURE coral.qryPayEmp8_newtmp @lQryCompanyNumber
> int,@dtQryPeriodFromDate datetime,@dtQryPeriodEndDate
> datetime,@dtQryPayrollYearStartDate datetime,@szQryCompanyTaxReference
> varchar(30) AS
--variables used for cursors
DECLARE
@lCCUniqueID
> INT,
@lUniqueID INT,
@lEmployeeNumber INT,
@szEmployeeSurname VARCHAR
> (20),
@szInitials VARCHAR (4),
@szDeptNo VARCHAR (6),
@dtDateLeft
> DATETIME,
@EdTotalGrossPayThis FLOAT,
@EdTaxableGrossPayThis FLOAT,
>
@EdTotalGrossPayPrevious FLOAT,
@EdTaxableGrossPayPrevious FLOAT,
>
@EdTaxPaidThis FLOAT,
@EdTaxPaidPrevious FLOAT,
@EdSMPPaidToDate
> FLOAT,
@EdSSPPaidToDate FLOAT,
@PdTotalGrossPayThis FLOAT,
>
@PdTaxableGrossPayThis FLOAT,
@PdTaxPaidThis FLOAT,
@PdSMPPaid FLOAT,
>
@PdSSPPaid FLOAT,
@szNICategory VARCHAR (1),

--output from
> qryEmployeesNIbyCat
@SumOfdNIablePay FLOAT,
@SumOfdEmployersNI FLOAT,
>
@SumOfdEmployeeNI FLOAT,
@SumOfdContractedOutEarnings FLOAT,
>
@SumOfdEmployeeNIContractedOut FLOAT,

--output from
> qryEmployeeNICHol
@SumOfdEmployersNICHoliday FLOAT

SET NOCOUNT
> ON

DECLARE employee_period CURSOR
FOR
SELECT
> tblEmployees.lUniqueID,
tblEmployees.lEmployeeNumber,

> tblEmployees.szEmployeeSurname,
tblEmployees.szInitials,

> tblEmployees.szDeptNo,
tblEmployees.dtDateLeft,

> tblEmployees.dTotalGrossPayThis,

> tblEmployees.dTaxableGrossPayThis,

> tblEmployees.dTotalGrossPayPrevious,

> tblEmployees.dTaxableGrossPayPrevious,
tblEmployees.dTaxPaidThis
> AS EdTaxPaidThis,
tblEmployees.dTaxPaidPrevious,

> tblEmployees.dSMPPaidToDate,
tblEmployees.dSSPPaidToDate

> FROM tblEmployees
WHERE (tblEmployees.lCompanyNumber =
> @lQryCompanyNumber)


DECLARE employee_ni_categories SCROLL CURSOR
FOR
>
SELECT DISTINCT
tblEmployeePeriodDetails.lUniqueID,

> tblEmployeePeriodDetails.cNICategory
FROM tblEmployeePeriodDetails

> WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND

> (tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))

>
OPEN employee_period

FETCH NEXT FROM employee_period INTO

> @lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,

> @szInitials,
@szDeptNo,
@dtDateLeft,

> @EdTotalGrossPayThis,
@EdTaxableGrossPayThis,

> @EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,

> @EdTaxPaidThis,
@EdTaxPaidPrevious,
@EdSMPPaidToDate,
>
@EdSSPPaidToDate

WHILE (@@FETCH_STATUS <> -1)
BEGIN

> EXEC qryPayEmp8b @lUniqueID,
@dtQryPayrollYearStartDate,

> @SumOfdEmployersNICHoliday OUTPUT
EXEC qryPayEmp8c
> @lUniqueID,
@dtQryPeriodEndDate,

> @PdTotalGrossPayThis OUTPUT,
@PdTaxableGrossPayThis OUTPUT,
>
@PdTaxPaidThis OUTPUT,
@PdSMPPaid OUTPUT,

> @PdSSPPaid OUTPUT
OPEN employee_ni_categories
FETCH
> FIRST FROM employee_ni_categories INTO
@lCCUniqueID,

> @szNICategory
IF (@@FETCH_STATUS <> -1)
WHILE
> (@@FETCH_STATUS <> -1)
BEGIN
EXEC qryPayEmp8a
> @lUniqueID,
@szNICategory,

> @dtQryPayrollYearStartDate,
@SumOfdNIablePay OUTPUT,

> @SumOfdEmployersNI OUTPUT,

> @SumOfdEmployeeNI OUTPUT,
@SumOfdContractedOutEarnings
> OUTPUT,
@SumOfdEmployeeNIContractedOut OUTPUT

>
SELECT @lUniqueID AS lUniqueID,

> @lEmployeeNumber AS lEmployeeNumber,

> @szEmployeeSurname AS szSurname,
@szInitials
> AS szInitials,
@szDeptNo AS szDeptNo,

> @dtDateLeft AS dtDateLeft,
@EdTotalGrossPayThis
> AS EdTotalGrossPayThis,
@EdTaxableGrossPayThis AS
> EdTaxableGrossPayThis,
@EdTotalGrossPayPrevious AS
> EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious AS
> EdTaxableGrossPayPrevious,
@EdTaxPaidThis AS
> EdTaxPaidThis,
@EdTaxPaidPrevious AS
> EdTaxPaidPrevious,
@EdSMPPaidToDate AS
> EdSMPPaidToDate,
@EdSSPPaidToDate AS
> EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS
> SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS
> PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS
> PdTaxableGrossPayThis,
@PdTaxPaidThis AS
> PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,

> @PdSSPPaid AS PdSSPPaid,
@szNICategory
> AS szNICategory,
@SumOfdNIablePay AS
> SumOfdNIablePay,
@SumOfdEmployersNI AS
> SumOfdEmployersNI,
@SumOfdEmployeeNI AS
> SumOfdEmployeeNI,
@SumOfdContractedOutEarnings AS
> SumOfdContractedOutEarnings,
@SumOfdEmployeeNIContractedOut
> AS SumOfdEmployeeNIContractedOut

FETCH NEXT FROM
> employee_ni_categories INTO
@lCCUniqueID,

> @szNICategory
END
ELSE
SELECT @lUniqueID
> AS lUniqueID,
@lEmployeeNumber AS lEmployeeNumber,
>
@szEmployeeSurname AS szSurname,

> @szInitials AS szInitials,
@szDeptNo AS
> szDeptNo,
@dtDateLeft AS dtDateLeft,

> @EdTotalGrossPayThis AS EdTotalGrossPayThis,

> @EdTaxableGrossPayThis AS EdTaxableGrossPayThis,

> @EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious,

> @EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious,

> @EdTaxPaidThis AS EdTaxPaidThis,

> @EdTaxPaidPrevious AS EdTaxPaidPrevious,

> @EdSMPPaidToDate AS EdSMPPaidToDate,
@EdSSPPaidToDate
> AS EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS
> SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS
> PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS
> PdTaxableGrossPayThis,
@PdTaxPaidThis AS
> PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,

> @PdSSPPaid AS PdSSPPaid
CLOSE employee_ni_categories
>
FETCH NEXT FROM employee_period INTO
@lUniqueID,

> @lEmployeeNumber,
@szEmployeeSurname,

> @szInitials,
@szDeptNo,
@dtDateLeft,

> @EdTotalGrossPayThis,
@EdTaxableGrossPayThis,

> @EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,

> @EdTaxPaidThis,
@EdTaxPaidPrevious,

> @EdSMPPaidToDate,
@EdSSPPaidToDate
END

CLOSE
> employee_period
DEALLOCATE employee_period

DEALLOCATE
> employee_ni_categories
RETURN

-----------------


CREATE PROCEDURE
> coral.qryPayEmp8a
@lUniqueID INT = 1,
@szNICategory VARCHAR (1) =
> &#39;A&#39;,
@dtPayrollYearStartDate DATETIME = &#39;1900-01-01
> 00:00:00.000&#39;,
@SumOfdNIablePay FLOAT OUTPUT,
@SumOfdEmployersNI
> FLOAT OUTPUT,
@SumOfdEmployeeNI FLOAT
> OUTPUT,
@SumOfdContractedOutEarnings FLOAT
> OUTPUT,
@SumOfdEmployeeNIContractedOut FLOAT OUTPUT
AS
SELECT DISTINCT
>
@SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay),

> @SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI),

> @SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI),

> @SumOfdContractedOutEarnings =
> Sum(tblEmployeePeriodDetails.dContractedOutEarning s),

> @SumOfdEmployeeNIContractedOut =
> Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out)
FROM
> tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID =
> @lUniqueID) AND
(tblEmployeePeriodDetails.cNICategory =
> @szNICategory) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >=
> @dtPayrollYearStartDate))
GROUP BY tblEmployeePeriodDetails.lUniqueID,
> tblEmployeePeriodDetails.cNICategory

RETURN

--------------------------
> ---

REATE PROCEDURE coral.qryPayEmp8b
@lQryUniqueID INT =
> 1,
@dtQryPayrollYearStartDate DATETIME = &#39;1900-01-01
> 00:00:00.000&#39;,
@SumOfdEmployersNICHoliday FLOAT OUTPUT
AS
SELECT

> @SumOfdEmployersNICHoliday =
> Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday)
FROM
> tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID =
> @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >=
> @dtQryPayrollYearStartDate))


RETURN

--------------------

CREATE
> PROCEDURE coral.qryPayEmp8c
@lQryUniqueID INT = 1,
@dtQryPeriodEndDate
> DATETIME = &#39;1900-01-01 00:00:00.000&#39;,
@PdTaxableGrossPayThis FLOAT
> OUTPUT,
@PdTaxPaidThis FLOAT OUTPUT,
@PdSMPPaid FLOAT OUTPUT,
>
@PdTotalGrossPayThis FLOAT OUTPUT,
@PdSSPPaid FLOAT OUTPUT
AS
SET
> NOCOUNT ON
SELECT @PdTotalGrossPayThis =
> tblEmployeePeriodDetails.dTotalGrossPayThis,
@PdTaxableGrossPayThis =
> tblEmployeePeriodDetails.dTaxableGrossPayThis ,
@PdTaxPaidThis =
> tblEmployeePeriodDetails.dTaxPaidThis,
@PdSMPPaid =
> tblEmployeePeriodDetails.dSMPPaid,
@PdSSPPaid =
> tblEmployeePeriodDetails.dSSPPaid

FROM
> tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID =
> @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate =
> @dtQryPeriodEndDate))


RETURN

[Leslee]

Are you connecting to SQL Server via an ODBC connection? I had a similar problem. The solution for me was to connect directly to the SQL server from Crystal and not go through ODBC. When I went through the ODBC connection, I got 1 row for a result set. When I connected directly to SQL Server I got the full result set.

On 10/30/98 3:01:10 AM, Martin Topping wrote:
> On 10/29/98 5:20:42 AM, Martin Topping wrote:
> We&#39;re having problems
> with a number of stored procedures we have
> written (we&#39;re all v.
> new to SQL Server). The typical scenario is
> :

sp executes spA,spB
> and spC
Each uses a cursor, spA,spB and spC out
> values that sp wants
> to output with some additional info.

In ISQL/W the
> output is fine.
> When the sp is executed from a Crystal Report we always get
> a single
> row of data (the 1st). We get the same problem if it is executed
> from
> the Access Upsizing Tool&#39;s SQL Server Browser utility.

We use
>
> SET NOCOUNT ON in sp (not in the others).
If we remove SET NOCOUNT ON we
>
> get now rows in Crystal.

I enclose an example (with 3 sub- SPs).
> Help.
> Martin


CREATE PROCEDURE coral.qryPayEmp8_newtmp
> @lQryCompanyNumber
> int,@dtQryPeriodFromDate
> datetime,@dtQryPeriodEndDate
> datetime,@dtQryPayrollYearStartDate
> datetime,@szQryCompanyTaxReference
> varchar(30) AS
--variables used for
> cursors
DECLARE
@lCCUniqueID
> INT,
@lUniqueID INT,
@lEmployeeNumber
> INT,
@szEmployeeSurname VARCHAR
> (20),
@szInitials VARCHAR (4),
>
@szDeptNo VARCHAR (6),
@dtDateLeft
> DATETIME,
@EdTotalGrossPayThis
> FLOAT,
@EdTaxableGrossPayThis FLOAT,
>
@EdTotalGrossPayPrevious
> FLOAT,
@EdTaxableGrossPayPrevious FLOAT,
>
@EdTaxPaidThis FLOAT,
>
@EdTaxPaidPrevious FLOAT,
@EdSMPPaidToDate
> FLOAT,
>
@EdSSPPaidToDate FLOAT,
@PdTotalGrossPayThis FLOAT,
>
>
@PdTaxableGrossPayThis FLOAT,
@PdTaxPaidThis FLOAT,
@PdSMPPaid FLOAT,
>
>
@PdSSPPaid FLOAT,
@szNICategory VARCHAR (1),

--output from
>
> qryEmployeesNIbyCat
@SumOfdNIablePay FLOAT,
@SumOfdEmployersNI FLOAT,
>
>
@SumOfdEmployeeNI FLOAT,
@SumOfdContractedOutEarnings FLOAT,
>
>
@SumOfdEmployeeNIContractedOut FLOAT,

--output from
>
> qryEmployeeNICHol
@SumOfdEmployersNICHoliday FLOAT

SET NOCOUNT
>
> ON

DECLARE employee_period CURSOR
FOR
SELECT
>
> tblEmployees.lUniqueID,
tblEmployees.lEmployeeNumber,

>
> tblEmployees.szEmployeeSurname,
tblEmployees.szInitials,

>
> tblEmployees.szDeptNo,
tblEmployees.dtDateLeft,

>
> tblEmployees.dTotalGrossPayThis,

>
> tblEmployees.dTaxableGrossPayThis,

>
> tblEmployees.dTotalGrossPayPrevious,

>
> tblEmployees.dTaxableGrossPayPrevious,
tblEmployees.dTaxPaidThis
>
> AS EdTaxPaidThis,
tblEmployees.dTaxPaidPrevious,

>
> tblEmployees.dSMPPaidToDate,
tblEmployees.dSSPPaidToDate

>
> FROM tblEmployees
WHERE (tblEmployees.lCompanyNumber =
>
> @lQryCompanyNumber)


DECLARE employee_ni_categories SCROLL CURSOR
FOR
>
>
SELECT DISTINCT
tblEmployeePeriodDetails.lUniqueID,

>
> tblEmployeePeriodDetails.cNICategory
FROM
> tblEmployeePeriodDetails

> WHERE
> ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND

>
> (tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))

>
>
OPEN employee_period

FETCH NEXT FROM employee_period INTO

>
> @lUniqueID,
@lEmployeeNumber,
@szEmployeeSurname,
>

> @szInitials,
@szDeptNo,
@dtDateLeft,

>
> @EdTotalGrossPayThis,
@EdTaxableGrossPayThis,

>
> @EdTotalGrossPayPrevious,
@EdTaxableGrossPayPrevious,

>
> @EdTaxPaidThis,
@EdTaxPaidPrevious,

> @EdSMPPaidToDate,
>
@EdSSPPaidToDate

WHILE (@@FETCH_STATUS
> -1)
BEGIN

> EXEC qryPayEmp8b @lUniqueID,

> @dtQryPayrollYearStartDate,

> @SumOfdEmployersNICHoliday
> OUTPUT
EXEC qryPayEmp8c
> @lUniqueID,

> @dtQryPeriodEndDate,

> @PdTotalGrossPayThis OUTPUT,

> @PdTaxableGrossPayThis OUTPUT,
>
@PdTaxPaidThis
> OUTPUT,
@PdSMPPaid OUTPUT,

> @PdSSPPaid
> OUTPUT
OPEN employee_ni_categories
FETCH
> FIRST FROM
> employee_ni_categories INTO
@lCCUniqueID,

>
> @szNICategory
IF (@@FETCH_STATUS -1)
WHILE
>
> (@@FETCH_STATUS -1)
BEGIN
EXEC qryPayEmp8a
>
> @lUniqueID,
@szNICategory,

>
> @dtQryPayrollYearStartDate,
@SumOfdNIablePay OUTPUT,

>
> @SumOfdEmployersNI OUTPUT,

>
> @SumOfdEmployeeNI OUTPUT,
@SumOfdContractedOutEarnings
>
> OUTPUT,
@SumOfdEmployeeNIContractedOut OUTPUT

>
>
SELECT @lUniqueID AS lUniqueID,

>
> @lEmployeeNumber AS lEmployeeNumber,

>
> @szEmployeeSurname AS szSurname,
@szInitials
>
> AS szInitials,
@szDeptNo AS szDeptNo,

>
> @dtDateLeft AS dtDateLeft,

> @EdTotalGrossPayThis
> AS EdTotalGrossPayThis,

> @EdTaxableGrossPayThis AS
> EdTaxableGrossPayThis,

> @EdTotalGrossPayPrevious AS
> EdTotalGrossPayPrevious,

> @EdTaxableGrossPayPrevious AS
> EdTaxableGrossPayPrevious,

> @EdTaxPaidThis AS
> EdTaxPaidThis,

> @EdTaxPaidPrevious AS
> EdTaxPaidPrevious,

> @EdSMPPaidToDate AS
> EdSMPPaidToDate,

> @EdSSPPaidToDate AS
> EdSSPPaidToDate,

> @SumOfdEmployersNICHoliday AS
> SumOfdEmployersNICHoliday,

> @PdTotalGrossPayThis AS
> PdTotalGrossPayThis,

> @PdTaxableGrossPayThis AS
> PdTaxableGrossPayThis,

> @PdTaxPaidThis AS
> PdTaxPaidThis,
@PdSMPPaid
> AS PdSMPPaid,

> @PdSSPPaid AS
> PdSSPPaid,
@szNICategory
> AS szNICategory,

> @SumOfdNIablePay AS
> SumOfdNIablePay,

> @SumOfdEmployersNI AS
> SumOfdEmployersNI,

> @SumOfdEmployeeNI AS
> SumOfdEmployeeNI,

> @SumOfdContractedOutEarnings AS
> SumOfdContractedOutEarnings,

> @SumOfdEmployeeNIContractedOut
> AS
> SumOfdEmployeeNIContractedOut

FETCH NEXT FROM
>
> employee_ni_categories INTO
@lCCUniqueID,

>
> @szNICategory
END
ELSE
SELECT @lUniqueID
>
> AS lUniqueID,
@lEmployeeNumber AS
> lEmployeeNumber,
>
@szEmployeeSurname AS szSurname,
>

> @szInitials AS szInitials,

> @szDeptNo AS
> szDeptNo,
@dtDateLeft
> AS dtDateLeft,

> @EdTotalGrossPayThis AS
> EdTotalGrossPayThis,

> @EdTaxableGrossPayThis AS
> EdTaxableGrossPayThis,

> @EdTotalGrossPayPrevious AS
> EdTotalGrossPayPrevious,

> @EdTaxableGrossPayPrevious AS
> EdTaxableGrossPayPrevious,

> @EdTaxPaidThis AS
> EdTaxPaidThis,

> @EdTaxPaidPrevious AS
> EdTaxPaidPrevious,

> @EdSMPPaidToDate AS
> EdSMPPaidToDate,
@EdSSPPaidToDate
> AS
> EdSSPPaidToDate,
@SumOfdEmployersNICHoliday AS
>
> SumOfdEmployersNICHoliday,
@PdTotalGrossPayThis AS
>
> PdTotalGrossPayThis,
@PdTaxableGrossPayThis AS
>
> PdTaxableGrossPayThis,
@PdTaxPaidThis AS
>
> PdTaxPaidThis,
@PdSMPPaid AS PdSMPPaid,

>
> @PdSSPPaid AS PdSSPPaid
CLOSE
> employee_ni_categories
>
FETCH NEXT FROM employee_period INTO

> @lUniqueID,

> @lEmployeeNumber,

> @szEmployeeSurname,

> @szInitials,
@szDeptNo,
>
@dtDateLeft,

> @EdTotalGrossPayThis,

> @EdTaxableGrossPayThis,

> @EdTotalGrossPayPrevious,

> @EdTaxableGrossPayPrevious,

> @EdTaxPaidThis,

> @EdTaxPaidPrevious,

> @EdSMPPaidToDate,

> @EdSSPPaidToDate
END

CLOSE
>
> employee_period
DEALLOCATE employee_period

DEALLOCATE
>
> employee_ni_categories
RETURN

-----------------


CREATE PROCEDURE
>
> coral.qryPayEmp8a
@lUniqueID INT = 1,
@szNICategory VARCHAR (1) =
>
> &#39;A&#39;,
@dtPayrollYearStartDate DATETIME = &#39;1900-01-01
>
> 00:00:00.000&#39;,
@SumOfdNIablePay FLOAT OUTPUT,
@SumOfdEmployersNI
>
> FLOAT OUTPUT,
@SumOfdEmployeeNI FLOAT
>
> OUTPUT,
@SumOfdContractedOutEarnings FLOAT
>
> OUTPUT,
@SumOfdEmployeeNIContractedOut FLOAT OUTPUT
AS
SELECT DISTINCT
>
>
@SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay),

>
> @SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI),

>
> @SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI),

>
> @SumOfdContractedOutEarnings =
>
> Sum(tblEmployeePeriodDetails.dContractedOutEarning s),

>
> @SumOfdEmployeeNIContractedOut =
>
> Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out)
FROM
>
> tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID =
>
> @lUniqueID) AND
(tblEmployeePeriodDetails.cNICategory =
>
> @szNICategory) AND
(tblEmployeePeriodDetails.dtPeriodEndDate >=
>
> @dtPayrollYearStartDate))
GROUP BY tblEmployeePeriodDetails.lUniqueID,
>
>
> tblEmployeePeriodDetails.cNICategory

RETURN

--------------------------
>
> ---

REATE PROCEDURE coral.qryPayEmp8b
@lQryUniqueID INT =
>
> 1,
@dtQryPayrollYearStartDate DATETIME = &#39;1900-01-01
>
> 00:00:00.000&#39;,
@SumOfdEmployersNICHoliday FLOAT OUTPUT
AS
SELECT

>
> @SumOfdEmployersNICHoliday =
>
> Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday)
FROM
>
> tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID =
>
> @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate
> >=
>
> @dtQryPayrollYearStartDate))


RETURN

--------------------

CREATE
>
> PROCEDURE coral.qryPayEmp8c
@lQryUniqueID INT =
> 1,
@dtQryPeriodEndDate
> DATETIME = &#39;1900-01-01
> 00:00:00.000&#39;,
@PdTaxableGrossPayThis FLOAT
> OUTPUT,
>
@PdTaxPaidThis FLOAT OUTPUT,
@PdSMPPaid FLOAT OUTPUT,
>
>
@PdTotalGrossPayThis FLOAT OUTPUT,
@PdSSPPaid FLOAT OUTPUT
AS
SET
>
> NOCOUNT ON
SELECT @PdTotalGrossPayThis =
>
> tblEmployeePeriodDetails.dTotalGrossPayThis,
@PdTaxableGrossPayThis =
>
> tblEmployeePeriodDetails.dTaxableGrossPayThis ,
@PdTaxPaidThis =
>
> tblEmployeePeriodDetails.dTaxPaidThis,
@PdSMPPaid =
>
> tblEmployeePeriodDetails.dSMPPaid,
@PdSSPPaid =
>
> tblEmployeePeriodDetails.dSSPPaid

FROM
>
> tblEmployeePeriodDetails
WHERE ((tblEmployeePeriodDetails.lUniqueID =
>
> @lQryUniqueID) AND
(tblEmployeePeriodDetails.dtPeriodEndDate
> =
> @dtQryPeriodEndDate))


RETURN