January 6, 2003 at 2:40 am
We have a Peculiar Problem where the stored procedure doesnt get executed for strange reason.let me explain the scenario
We have a DB table which is updated using four stored procudres called from the main stored procedure.
the main stored procedure is called from the VB program.
This Progam executes 99 out of 100 times,on one occasion it doesnt executes one/all child SP's.
only one user is made of execute this program at a time.
Can anyone explain why i face this problem
January 6, 2003 at 4:24 am
When involving child SPs you have to be carefull your logic doesn't have any loopholes in it. I found one once where I was doing IF..BEGIN...END over and over, however due to a logic issue I ended up in the wrong place and not having a final option if none of the criteria were met so when it didn't fit it seemed to never execute. After rechecking the logic I found my final IF should have been an ELSE instead and that corrected the problem. Odds are something in you logic is goind around what you expect. It will most likely help to run Profiler to catch all the executions to see if anything specific is being submitted when it doesn't seem to work. Also, post your code here if you would like someone to help look over it, as hundreds of sets of eyes are better than 1.
January 6, 2003 at 10:20 pm
hi Antares686,
now i have made all stored procedure into one and havent heard problems from the client yet(for the past 3 days).
Still i am puzzled why this has happnd,anyway i will post the code today
January 7, 2003 at 11:04 pm
Here is the stored procedure code:-
Create proc sp_ActsDetailsNew (@fromdate as datetime, @todate as Datetime, @compname as char(30), @linked as char(1), @retValue as int OUTPUT)
as
BEGIN TRAN
Set @retValue = 0
WHILE (select count(*) From TempAcctLEdgerRpt where compname = @compname) > 0
begin
Delete From TempAcctLEdgerRpt Where CompName = @compname
end
WHILE (select count(*) From TempAcctLEdgerRpt1 where compname = @compname) > 0
begin
Delete From TempAcctLEdgerRpt1 Where CompName = @compname
end
Declare @TODATE1 as datetime
set @TODATE1 = @todate
set @TODATE1 = cast(@TODATE1 as datetime)
BEGIN
execute GetActBalancesNew @fromdate,'',0,@compname
Set @retValue = 1
end
insert tempacctledgerrpt (head_Code,head_date, partyname,opening_bal,
compname, accountcode, accountname, fromdate, todate, receipt, payment, balance, sh_name,
linkcode)
select 'Z', @fromdate,'Opening Balance as on ' + rtrim(cast(@fromdate as char(12))), sum(t.opening_bal), @compname,
a.account_Code, account_name,@fromdate, @todate, 0,0, 1, t.sh_name, a.linkcode
from tempacctledgerrpt t, account_master a
where t.head_code = a.account_Code
group by a.account_code,t.sh_Name, account_name, a.linkcode
order by t.sh_name
BEGIN
delete from tempacctledgerrpt where partyname is null and compname = @CompName
end
Update TempAcctLedgerRpt
Set ContactPerson = (Select Contact_person From Dealer_Master Where Dealer_Code = AccountCode),
Tel = (Select Dealer_Tel1 From Dealer_Master Where Dealer_Code = AccountCode),
Fax = (Select Dealer_Fax1 From Dealer_Master Where Dealer_Code = AccountCode),
Address = (Select Dealer_Add1 + ', ' + Dealer_Add2 From Dealer_Master Where Dealer_Code = AccountCode)
Where CompName = @CompName
if @Linked =1
BEGIN
Update TempAcctLedgerRpt
Set Linked = 1
where linkcode <> ''
and compname = @compname
Update TempAcctlEdgerRpt
set Linkshname = (Select sh_name from account_master
where compname = @compname and linkcode <> '' and
TempAcctlEdgerRpt.linkcode = account_master.account_Code),
linkaccount = (Select account_name from account_master
where linkcode <> '' and
TempAcctlEdgerRpt.linkcode = account_master.account_Code)
end
update tempacctledgerrpt
set NextMonthDealer = (select Process_Next from dealer_master
where dealer_code = accountcode and compname = @compname)
Update tempacctledgerrpt
set nextmonthdealer = 0
where nextmonthdealer is null
and compname = @compname
update tempacctledgerrpt
set todate = dateadd("mm",2,todate)
where nextmonthdealer = 1
and compname = @compname
update tempacctledgerrpt
set todate = cast(year(todate) as char(4)) + '/' + rtrim(cast(month(todate) as char(2))) + '/01'
where nextmonthdealer = 1
and compname = @compname
update tempacctledgerrpt
set todate = dateadd("d",-1,todate),
@TODATE1 = dateadd("d",-1,@TODATE1)
where nextmonthdealer = 1
and compname = @compname
set @TODATE1 = (select top 1 todate from tempacctledgerrpt where compname = @compname and nextmonthdealer = 1)
set @TODATE1 = isnull(@TODATE1,@todate)
set @TODATE1 = cast(@TODATE1 aS DATETIME)
print @TODATE1
if @Linked = 1
BEGIN
Declare @Link as char(50), @dtDate as datetime, @CompName1 as char(30)
Declare GetLinkDetails Cursor For
select distinct linkcode, todate from tempacctledgerrpt
where linked=1 and compname = @compname
open GetLinkDetails
set @CompName1 = @compname
fetch next from GetLinkDetails
into @Link, @dtDate
while @@fetch_status = 0
begin
exec sp_LinkDetails @dtDate, @CompName1, @Link
fetch next from GetLinkDetails
into @Link, @dtDate
end
close GetLinkDetails
Deallocate GetLinkDetails
insert tempacctledgerrpt1 (head_Code,head_date, partyname,opening_bal,
compname, accountcode, accountname, fromdate, todate, receipt, payment, balance, sh_name,
linkcode)
select 'Z', @dtDate,'Opening Balance as on ' + cast(@dtDate as char(10)), sum(t.opening_bal), @compname,
a.account_Code, account_name,@dtDate, @dtDate, 0,0, 1, t.sh_name, a.linkcode
from tempacctledgerrpt1 t, account_master a
where t.head_code = a.account_Code
group by a.account_code,t.sh_Name, account_name, a.linkcode
delete from tempacctledgerrpt1 where partyname is null
Update TempAcctLedgerRpt
Set LinkBalance = (Select opening_bal from TempAcctLedgerRpt1
where TempAcctLedgerRpt1.accountcode = TempAcctLedgerRpt.LinkCode)
where linked =1
end
BEGIN
exec GetDetails @fromdate, @TODATE1, @compname
Set @retValue = 1
end
BEGIN
exec UpdateRest @compname
Set @retValue = 1
end
COMMIT TRAN
GO
January 8, 2003 at 5:51 am
Looking this over I made several changes that should increase the effency of the code but do test it before you make the changes. Also, noted a second spot where you do
if @Linked = 1
should one be
if @Linked != 1
if so then verify your logic a to which one should be. Id the second one then replace
if @Linked = 1
with
END
ELSE
Also, you where missing and END to you first if "@Linked = 1"'s BEGIN so I removed the second ones BEGIN when I commented.
Hope this helps.
Create proc sp_ActsDetailsNew (@fromdate as datetime, @todate as Datetime, @compname as char(30), @linked as char(1), @retValue as int OUTPUT)
as
/* SET NOCOUNT ON *//* You may want to add this as it does have some bennefits, however I don't no your code that calls this SP so may cause issues, you may want to test.*/
BEGIN TRAN
Set @retValue = 0
Delete From TempAcctLEdgerRpt Where CompName = @compname /* No while needed since you are deleting all @compname related values. */
Delete From TempAcctLEdgerRpt1 Where CompName = @compname /* No while needed since you are deleting all @compname related values. */
Declare @TODATE1 as datetime
set @TODATE1 = @todate /* No need to cast as value is already datetime. */
BEGIN
execute GetActBalancesNew @fromdate,'',0,@compname
Set @retValue = 1
end
insert tempacctledgerrpt (
head_Code,
head_date,
partyname,
opening_bal,
compname,
accountcode,
accountname,
fromdate,
todate,
receipt,
payment,
balance,
sh_name,
linkcode
)
select
'Z',
@fromdate,
'Opening Balance as on ' + convert(varchar,@fromdate,107), /* Use convert here is simpler in memory as format already available. */
sum(t.opening_bal),
@compname,
a.account_Code,
account_name,
@fromdate,
@todate,
0,
0,
1,
t.sh_name,
a.linkcode
from
tempacctledgerrpt t
INNER JOIN /* Use Inner Join to Comply with newer SQL Standards, there are some compiler advantages as well. */
account_master a
ON /* ON is the where clause for the join it self. */
t.head_code = a.account_Code
group by
a.account_code,
t.sh_Name,
account_name,
a.linkcode
order by t.sh_name /* Now this you may want to remove, all this does is preorder the data going in but that requires it to copy all the data to TempDB first increasing overhead. Better to look at a clustered index on tempacctledgerrpt table . */
/*delete from tempacctledgerrpt where partyname is null and compname = @CompName *//* based on 'Opening Balance as on ' + convert(varchar,@fromdate,107) as the third input NULL will not occurr don't need to bother unless I missed something. */
/* Sorry previous was bad coding and causes a lot more reads than you need. */
Update
TempAcctLedgerRpt
Set
ContactPerson = DM.Contact_person,
Tel = DM.Dealer_Tel1,
Fax = DM.Dealer_Fax1,
Address = (DM.Dealer_Add1 + ', ' + DM.Dealer_Add2) /* May want to do an ISNULL() function to validate Add1 or Add2 since if a NULL is concatinated in the output is NULL. */
FROM
Dealer_Master DM
INNER JOIN
TempAcctLedgerRpt TALR
ON
DM.Dealer_Code = TALR.AccountCode
WHERE
TALR.CompName = @CompName
if @Linked = 1
BEGIN
Update TempAcctLedgerRpt
Set Linked = 1
where linkcode <> ''
and compname = @compname
Update TempAcctlEdgerRpt
set Linkshname = (Select sh_name from account_master
where compname = @compname and linkcode <> '' and
TempAcctlEdgerRpt.linkcode = account_master.account_Code),
linkaccount = (Select account_name from account_master
where linkcode <> '' and
TempAcctlEdgerRpt.linkcode = account_master.account_Code)
end
update
tempacctledgerrpt
set
NextMonthDealer = ISNULL(Process_Next, 0), /* This combines 2 actions into one to reduce writes. */
todate = (CASE
Process_Next
WHEN 1
THEN dateadd(d,-1,convert(varchar,dateadd(mm,2,dateadd(d,-(datepart(d,@fromdate)-1),@fromdate)),101))
ELSE todate
END) /* Combines in 3 additional updates further reducing reads/writes. */
FROM
Dealer_Master DM
LEFT JOIN /* By using left join whe will get a NULL value from the Dealer_Code values when no match. */
TempAcctLedgerRpt TALR
ON
DM.Dealer_Code = TALR.AccountCode
WHERE
TALR.CompName = @CompName
set @TODATE1 = (select top 1 ISNULL(todate,@todate) from tempacctledgerrpt where compname = @compname and nextmonthdealer = 1) /* Saves an extra set. */
print @TODATE1
--if @Linked = 1 /* Didn't see anywhere this was reset so shouldn't need. Verify this isn't a logic error.*/
--BEGIN
/* Start -- Fuzzy on the logic here since I cannot see what it is doing in the SP but if possible you should work to avoid cursors. */
Declare @Link as char(50), @dtDate as datetime, @CompName1 as char(30)
Declare GetLinkDetails Cursor For
select distinct linkcode, todate from tempacctledgerrpt
where linked=1 and compname = @compname
open GetLinkDetails
set @CompName1 = @compname
fetch next from GetLinkDetails
into @Link, @dtDate
while @@fetch_status = 0
begin
exec sp_LinkDetails @dtDate, @CompName1, @Link
fetch next from GetLinkDetails
into @Link, @dtDate
end
close GetLinkDetails
Deallocate GetLinkDetails
/* End -- Fuzzy on the logic here since I cannot see what it is doing in the SP but if possible you should work to avoid cursors. */
insert tempacctledgerrpt1 (
head_Code,
head_date,
partyname,
opening_bal,
compname,
accountcode,
accountname,
fromdate,
todate,
receipt,
payment,
balance,
sh_name,
linkcode)
select
'Z',
@dtDate,
'Opening Balance as on ' + convert(varchar,@dtDate,107), /* Use convert here is simpler in memory as format already available. */
sum(t.opening_bal),
@compname,
a.account_Code,
account_name,
@dtDate,
@dtDate,
0,
0,
1,
t.sh_name,
a.linkcode
from
tempacctledgerrpt1 t
INNER JOIN /* Use Inner Join to Comply with newer SQL Standards, there are some compiler advantages as well. */
account_master a
ON /* ON is the where clause for the join it self. */
t.head_code = a.account_Code
group by
a.account_code,
t.sh_Name,
account_name,
a.linkcode
/*delete from tempacctledgerrpt1 where partyname is null*//* based on 'Opening Balance as on ' + convert(varchar,@dtDate,107) as the third input NULL will not occurr don't need to bother unless I missed something. */
/* Sorry previous was bad coding and causes a lot more reads than you need. */
Update
TempAcctLedgerRpt
Set
LinkBalance = TALR1.opening_bal
from
TempAcctLedgerRpt1 TALR1
INNER JOIN
TempAcctLedgerRpt TALR
ON
TALR1.accountcode = TALR.LinkCode
WHERE TALR.linked =1
END
BEGIN
exec GetDetails @fromdate, @TODATE1, @compname
--Set @retValue = 1 /* Why? If you are going to do in next statement anyway.*/
END
BEGIN
exec UpdateRest @compname
--Set @retValue = 1 /* Why? Here as it seems to be set when you get to this point no matter the case.*/
END
SET @retVal = 1 /* This looks more logical based on what you actually are doing. */
COMMIT TRAN
GO
January 11, 2003 at 3:26 am
Thnaks a lot buddy,if u can give me u r yahoo messenger/MSN messenger Id i will add as my friend and can have regular chats,if u r intrested in
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply