facing - The SQL Server is terminating this process error
Posted by: shubhada khawase
Date: September 25, 2006 02:56AM
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
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
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.