MySQL Forums
Forum List  »  Sybase

facing - The SQL Server is terminating this process error
Posted by: shubhada khawase
Date: September 25, 2006 02:56AM

Hi All,
I have one proceudre for which I am facing problem. when I execute this procedure I got the following error

Server Message: Number 5702, Severity 10
Procedure 'cm_find_ratetable', Line 57:
The SQL Server is terminating this process.
Client Message: Layer 1, Origin 1, Severity 1, Number 167
ct_cmd_drop(): user api layer: external error: This routine cannot be called because the command structure is in an undefined state.

Following is my procedure :
CREATE proc cm_find_ratetable
(
@payerpricingname varchar(30)= NULL,
@rulename varchar(30)= NULL,
@contractid varchar(40) = NULL,
@payerid varchar(20)= NULL,
@alldates varchar(10)=NULL,
@sortfield varchar(30)=NULL,
@sortorder varchar(10) = NULL
)
As
Begin

declare @percent char(1),
@orderby numeric,
@maxeffective smalldatetime

select @percent = '%'


if(@sortfield= 'PayerPricingName')
select @orderby =1
else if (@sortfield= 'RuleName')
select @orderby =2
else if (@sortfield= 'Effective')
select @orderby =3
else if (@sortfield= 'Termination')
select @orderby =4


if @payerpricingname !='%'
select @payerpricingname = @payerpricingname
else
select @payerpricingname = @percent


if @rulename !='%'
select @rulename = @rulename
else
select @rulename = @percent

if @contractid !='%'
select @contractid = @contractid
else
select @contractid = @percent

if @payerid !='%'
select @payerid = @payerid
else
select @payerid = @percent


if (@alldates = 'Y')
Begin
if (@sortorder = 'D')
begin
select PayerPricingKey,
PayerPricingName,
RuleName,
Effective,
Termination
from
(select distinct
PayerPricingKey,
PayerPricingName,
PR.RuleName ,
PR.Effective,
PR.Termination
from PayerPricing PR ,
TermInRule TIR ,
TermRule TR ,
ContractTerms CT ,
LookCCntrct LCC ,
PayerProfile PP
where TR.TermKey = CT.TermKey and
LCC.ContractID = CT.ContractID and
TIR.TermRuleKey = TR.TermRuleKey and
PR.PayerPricingName = TIR.InputValue and
PR.PayerPricingName like (@payerpricingname) and
PR.RuleName like (@rulename) and
LCC.ContractID in (select distinct ContractID from PayerContracts
join PayerProfile on
PayerContracts.PUID = PayerProfile.PUID and PayerID like (@payerid)) and
LCC.ContractID like (@contractid)
)RatesTbl
ORDER BY CASE
WHEN @orderby= 1 THEN PayerPricingName
WHEN @orderby= 2 THEN RuleName
WHEN @orderby= 3 THEN convert(varchar,Effective,120)
WHEN @orderby= 4 THEN convert(varchar,Termination,120)
ELSE PayerPricingName
END
DESC
end

else
begin
select PayerPricingKey,
PayerPricingName,
RuleName,
Effective,
Termination
from
(select distinct
PayerPricingKey,
PayerPricingName,
PR.RuleName ,
PR.Effective,
PR.Termination
from
PayerPricing PR ,
TermInRule TIR ,
TermRule TR ,
ContractTerms CT ,
LookCCntrct LCC ,
PayerProfile PP
where TR.TermKey = CT.TermKey and
LCC.ContractID = CT.ContractID and
TIR.TermRuleKey = TR.TermRuleKey and
PR.PayerPricingName = TIR.InputValue and
PR.PayerPricingName like (@payerpricingname) and
PR.RuleName like (@rulename) and
LCC.ContractID in (select distinct ContractID from PayerContracts
join PayerProfile on
PayerContracts.PUID = PayerProfile.PUID and PayerID like (@payerid)) and
LCC.ContractID like (@contractid)
) RatesTbl
ORDER BY CASE
WHEN @orderby =1 THEN PayerPricingName
WHEN @orderby= 2 THEN RuleName
WHEN @orderby = 3 THEN convert(varchar,Effective,120)
WHEN @orderby = 4 THEN convert(varchar,Termination,120)
ELSE PayerPricingName
END
end

end

else
if (@sortorder = 'D')
begin
select PayerPricingKey,
PayerPricingName,
RuleName,
Effective,
Termination
from
(select distinct
PayerPricingKey,
PayerPricingName,
PR.RuleName ,
PR.Effective,
PR.Termination
from
PayerPricing PR ,
TermInRule TIR ,
TermRule TR ,
ContractTerms CT ,
LookCCntrct LCC ,
PayerProfile PP
where PR.Effective = (select Max(Effective) from PayerPricing PR1 where
PR.PayerPricingName = PR1.PayerPricingName ) and
TR.TermKey = CT.TermKey and
LCC.ContractID = CT.ContractID and
TIR.TermRuleKey = TR.TermRuleKey and
PR.PayerPricingName = TIR.InputValue and
PR.PayerPricingName like (@payerpricingname) and
PR.RuleName like (@rulename) and
LCC.ContractID in (select distinct ContractID from PayerContracts
join PayerProfile on
PayerContracts.PUID = PayerProfile.PUID and PayerID like (@payerid)) and
LCC.ContractID like (@contractid)
) RatesTbl
ORDER BY CASE
WHEN @orderby =1 THEN PayerPricingName
WHEN @orderby= 2 THEN RuleName
WHEN @orderby = 3 THEN convert(varchar,Effective,120)
WHEN @orderby = 4 THEN convert(varchar,Termination,120)
ELSE PayerPricingName
END
DESC
end

else
begin
select PayerPricingKey,
PayerPricingName,
RuleName,
Effective,
Termination
from
(select distinct
PayerPricingKey,
PayerPricingName,
PR.RuleName ,
PR.Effective,
PR.Termination
from PayerPricing PR ,
TermInRule TIR ,
TermRule TR ,
ContractTerms CT ,
LookCCntrct LCC ,
PayerProfile PP
where PR.Effective = (select Max(Effective) from PayerPricing PR1 where
PR.PayerPricingName = PR1.PayerPricingName ) and
TR.TermKey = CT.TermKey and
LCC.ContractID = CT.ContractID and
TIR.TermRuleKey = TR.TermRuleKey and
PR.PayerPricingName = TIR.InputValue and
PR.PayerPricingName like (@payerpricingname) and
PR.RuleName like (@rulename) and
LCC.ContractID in (select distinct ContractID
from PayerContracts join PayerProfile on
PayerContracts.PUID = PayerProfile.PUID and PayerID like (@payerid)) and
LCC.ContractID like (@contractid)
) RatesTbl
ORDER BY
CASE
WHEN @orderby =1 THEN PayerPricingName
WHEN @orderby= 2 THEN RuleName
WHEN @orderby = 3 THEN convert(varchar,Effective,120)
WHEN @orderby = 4 THEN convert(varchar,Termination,120)
ELSE PayerPricingName
END
end
end


Please help me to resolve this problem

Options: ReplyQuote


Subject
Views
Written By
Posted
facing - The SQL Server is terminating this process error
10793
September 25, 2006 02:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.