my SP looks like run forever. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

my SP looks like run forever.

Author

Topic

jim65
Starting Member

35 Posts

Posted - 2006-04-10 : 13:43:52

I have a stored procedure which insert the record into the adReportingRequirement table. then call user defined function to get a due date. after that, insert the record into the adReportingRequirementHistory table, it looks like run forerver and didn't insert any data into the adReportingRequirementHistory table.

CREATE PROCEDURE asp_InsertReportingRequirement_test (@EntityID int, @RptReqTypeID int, @FrequencyID int, @DaysUntilDue int, @StartDate smalldatetime, @EndDate smalldatetime)

AS
set nocount on
--inserts record into reportingRequirement table using parameters passed in from the front end

INSERT INTO adReportingRequirement
(EntityID, RptReqTypeID, FrequencyID, DaysUntilDue, StartDate, EndDate)
VALUES (@EntityID, @RptReqTypeID, @FrequencyID, @DaysUntilDue, @StartDate, @EndDate)
--get my newly inserted record ID

Declare @NewRecord int
SELECT @NewRecord = @@Identity

DECLARE @FYE AS smalldatetime
SELECT @FYE = ContactFYE FROM dashboard.dbo.adBorrowerEntity
Where EntityID = @EntityID

-- calls the User Defined Function dbo.udfNEW_DueDate to get my due date based on the start date and due date from the user

Declare @DateDue smalldatetime
DECLARE @MonthCount as int
SET @MonthCount =1
Declare @ExpireDate smalldatetime

begin
while @MonthCount < =12
Select @DateDue = dbo.udfNEW_DueDate(@FYE, @DaysUntilDue,@MonthCount)
-- calls the User Defined Function udfNextHistory to get my expiration date based on the start date and frequency from the user
Select @ExpireDate= dbo.udfNextHistory(@FYE, @FrequencyID)
-- Inserts the first History record into the Reporting requirement history table with my newly created Identity from the Reporting Requirement table and defined dates above
INSERT INTO adReportingRequirementHistory
(RptReqID, PeriodDate, DateDue, ExpireDate)
VALUES (@NewRecord, @StartDate, @DateDue, @ExpireDate)
SET @MonthCount = @MonthCount + 1
end

go

CREATE FUNCTION dbo.udfNEW_DueDate (@FYE smalldatetime, @Duedays int, @monthCount int )
RETURNS smalldatetime AS
BEGIN
DECLARE @Return_temp_String AS smalldatetime
DECLARE @ReturnString AS smalldatetime

select @Return_temp_String = (select convert (datetime, convert (Char(8), ( DATEADD (dd , @Duedays, @FYE) ), 112)))

select @ReturnString = (select convert (datetime, convert (Char(8), ( DATEADD (mm , @monthCount, @Return_temp_String) ), 112)))

Return @ReturnString

END

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-10 : 13:53:30

I think the following may be the cause


begin 
while @MonthCount < =12
begin 

Select @DateDue = dbo.udfNEW_DueDate(@FYE, @DaysUntilDue,@MonthCount)
-- calls the User Defined Function udfNextHistory to get my expiration date based on the start date and frequency from the user
Select @ExpireDate= dbo.udfNextHistory(@FYE, @FrequencyID)
-- Inserts the first History record into the Reporting requirement history table with my newly created Identity from the Reporting Requirement table and defined dates above
INSERT INTO adReportingRequirementHistory
(RptReqID, PeriodDate, DateDue, ExpireDate)
VALUES (@NewRecord, @StartDate, @DateDue, @ExpireDate)
SET @MonthCount = @MonthCount + 1
end

Srinika

jim65
Starting Member

35 Posts

Posted - 2006-04-10 : 14:09:15

Thanks, you are right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources