August 20, 2002 at 6:38 pm
Help! I am attempting to run the following stored procedure in SQL Server 2000. The view in the select statement aggregates the Accounting Dates by one field (contract) and returns the maximum. The result of this query (with proper parameters passed) is the message 'This Stored Procedure has executed successfully.' Unfortunately, it does not pass back any of the output paramaters and appears to stop the entire process.
I have narrowed the problem down to the 'Between' statement and have tried >= and <= to no avail. Does anyone have any ideas on why this stored proc would fail?
Thanks.
Alter Procedure zzSue
@MaxDatedatetime = '01/01/1900',
@DaysAgeint= 0,
@PL_Amt decimal(15,2)= 0OUTPUT,
@ContractCountint= 0OUTPUT,
@MinDatedatetime= '01/01/1900'OUTPUT
As
declare @lContractCountint,
@lPL_Amtdecimal(15,2)
set @MinDate = DateAdd(day, -@DaysAge, @MaxDate)
select@lContractCount = Count(*), -- as ContractCount,
@lPL_Amt = sum(TranAmt_ONL) + sum(TranAmt_AP) --as PL_Amt
fromvw_lp_valid_contract_summary
where (MaxAccountingDate between @MinDate and @MaxDate)
and
(
ABSTranAmt_ONL <> 0
or ABSTranAmt_AP <> 0
)
and
(
ABSGrossEquipCost = 0
and ABSAdditionalIncome = 0
and ABSUpfrontTax = 0
and ABSContractAdjAmt = 0
)
set @ContractCount = @lContractCount
set @PL_Amt = @lPL_Amt
August 20, 2002 at 11:40 pm
how are you executing the procedure?!!
August 21, 2002 at 1:58 am
Just to add to GRN's reply ...
When you execute the procedure, you should also specify the parameters as OUTPUT. A correct call could be :
DECLARE @PL_Amt decimal(15,2)
DECLARE @ContractCount int
DECLARE @MinDate datetime
EXEC zzSue '01/01/2000',1, @PL_Amt OUTPUT,
@ContractCount OUTPUT, @MinDate OUTPUT
PRINT @PL_Amt
PRINT @ContractCount
PRINT @MinDate
August 21, 2002 at 9:07 am
Thank you for the responses.
I am attempting to execute the Stored Proc from another stored proc. I have used the correct OUTPUT identifiers for the OUTPUT parameters.
Here are some more strange things about this stored proc:
1. If I simply return a recordset instead of assigning the output variables to the recordset, the stored procedure will return the correct records.
2. If I call the stored procedure from another stored procedure (as in the response re: OUTPUT parameters), the calling procedure will not return a recordset at all. IE, it stops somewhere in the middle.
3. If I call the OUTPUT parameters via ADO/OLE, I get the correct values. However, I would really like to call this stored procedure repeatedly from another stored proc and return the results to a Crystal report.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply