Query batch completed with errors...

Hi,
I am executin a stored procedure which contains a sequence of Stored
procedures. One of these stored procedures is a cursor-stored procedure
(basically a cursor).
At the end of the execution of this Stp(cursor), it gives me "query batch
completed with errors". I tried to capture the error through Printing
@.@.error, but to no luck. The problem is that this stp is part of a sequence
of stored procedures. Because of this error the subsequent stps dont get
executed.
Could someone give some leads how to resolve this?
Thanx,
S.How are you executing the proc? Did you try running it in isolation in Query
Analyzer? That should at least show you the error message in the results
window. Debug the proc to find the statement that throws the error (In
Query Analyzer's Object Browser right-click the proc name and select Debug).
If you're still stuck once you've narrowed it down a bit then post some code
here to reproduce the error.
David Portas
SQL Server MVP
--|||David,
Yes I was executing it in the Analyser..that how i found out it was this stp
which was givin the prob...
Here is the code for the problem stp..
---
CREATE PROCEDURE CALC_MyDealValue_As_DealPartner
AS
DECLARE
@.pCustomerCivilId nvarchar(15),
@.pInvoiceNo nvarchar(50),
@.pLiability char(1),
@.pAppId char(3),
@.pGrossBalance decimal(15,3),
@.pShareHolderCivilID nvarchar(15),
@.pPartnerShare decimal(15,3)
--Declare the copy cursor
DECLARE DealPartner_Cursor CURSOR FOR
SELECT CustomerCivilId,InvoiceNumber,Liability,
AppId,ShareHolderCivilID,
Convert(Decimal(15,3),SharePercentage)/100 as share
FROM dbo.Shareholder
--where Left(CustomerCivilId,2) ='JA'
where (InvoiceNumber<>'') and (Liability <> '') and (AppId <>'') --For
Deal Partner This is not empty in Shareholder Table
--Open the cursor
BEGIN
OPEN DealPartner_Cursor
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 1' + convert(nvarchar(50),@.@.Error)
End
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Print 'Parnter- ' + convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Share- ' + convert(nvarchar(50),@.pPartnerShare)
SET @.pGrossBalance = (SELECT isnull(GrossBalance,0) FROM vwMyDealAmounts
WHERE ( --dbo.vwMyDealAmounts.CivilID = @.pCustomerCivilId and
dbo.vwMyDealAmounts.InvoiceNumber = @.pInvoiceNo and
dbo.vwMyDealAmounts.Liability = @.pLiability and
dbo.vwMyDealAmounts.AppId = @.pAppId))
IF @.@.Error <>0
Begin
Print 'Fetch GB ' + convert(nvarchar(50),@.@.Error)
End
--Print 'GrossBal - ' + convert(nvarchar(50),@.pGrossBalance)
--Print 'DealCivilId - ' + convert(nvarchar(50),@.pDealCivilID)
Set @.pGrossBalance = isnull(@.pGrossBalance,0)
if (@.pGrossBalance <> 0.000)
Begin
if Left(@.pCustomerCivilId,2) ='JA'
Begin
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pCustomerCivilId -- update TO JA
End
IF @.@.Error <>0
Begin
Print 'UP JA ' + convert(nvarchar(50),@.@.Error)
End
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pShareHolderCivilID
IF @.@.Error <>0
Begin
Print 'UP NON-JA ' + convert(nvarchar(50),@.@.Error)
End
--Print 'Done for ' + convert(nvarchar(50),@.pCustomerCivilId) + ' - ' +
convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Done for Count ' + convert(nvarchar(50),@.iCount)
End
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 2 ' + convert(nvarchar(50),@.@.Error)
End
END
CLOSE DealPartner_Cursor
DEALLOCATE DealPartner_Cursor
END
GO
---
"David Portas" wrote:

> How are you executing the proc? Did you try running it in isolation in Que
ry
> Analyzer? That should at least show you the error message in the results
> window. Debug the proc to find the statement that throws the error (In
> Query Analyzer's Object Browser right-click the proc name and select Debug
).
> If you're still stuck once you've narrowed it down a bit then post some co
de
> here to reproduce the error.
> --
> David Portas
> SQL Server MVP
> --
>
>|||You still didn't tell us the error message. Did you run it in Debug?
Honestly I wouldn't bother though. You should always try to avoid cursors
and 90% of this code is redundant. The effect seems to be the same as that
of a single UPDATE statement.
The following is my best guess of what you need to do. It's untested and as
it's done without a spec you'll probably have to make some mods. In
particular you may want to add WHERE EXISTS depending on requirements.
CREATE PROCEDURE calc_mydealvalue_as_dealpartner
AS
UPDATE TmpMyDealings
SET valasdealpartner = valasdealpartner +
(SELECT ISNULL(SUM(D.grossbalance*
CONVERT(DECIMAL(15,3),S.sharepercentage)/100),0)
FROM vwMyDealAmounts AS D
JOIN Shareholder AS S
ON D.invoicenumber = S.invoicenumber
AND D.liability = S.liability
AND D.appid = S.appid
AND D.grossbalance <>0
AND tmpmydealings.civilid = S.customercivilid
AND S.customercivilid LIKE 'JA%')
RETURN
GO
If you need more help please post DDL, a few rows of sample data (INSERT
statements are the best way to post sample data) and show us what result you
require from the sample. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--