Error in last line near end

Question

While i Execute a stored procedure I don't know why showing error in last line... I can't find any error with it

The error is telling

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 60

_

CREATE PROCEDURE `PartyBalanceViewByLedgerId`

    (
    p_ledgerId varchar(50),
    p_crOrDr varchar(50),
    p_branchId varchar(50)
    )

BEGIN

IF (p_crOrDr='Dr')
THEN
    SELECT 
    TEMP.voucherNo +'_'+ TEMP.voucherType AS ID,
    TEMP.voucherType,
    CASE WHEN (TEMP.voucherType = 'Receipt Voucher')
    THEN
        (SELECT receiptMasterId FROM tbl_ReceiptMaster 
            WHERE (receiptMasterId = TEMP.voucherNo))
    ELSE
        (SELECT purchaseMasterId FROM tbl_PurchaseMaster
         WHERE (purchaseMasterId = TEMP.voucherNo)) 
    END AS voucherNo,
    CAST(CAST(TEMP.balance AS DECIMAL(24,2)) AS char(27)) 
    AS amount
    FROM(SELECT 
        A.voucherNo,
        A.voucherType,
        (SUM(ifnull(A.credit, 0)) - SUM(ifnull(A.debit, 0)))
         AS balance 
        FROM tbl_PartyBalance AS A 
        WHERE (A.voucherType = 'Purchase Invoice' 
            OR A.voucherType = 'Receipt Voucher') 
        AND A.ledgerId=p_ledgerId 
        AND A.branchId=p_branchId 
        AND A.optional='False'
        GROUP BY A.voucherNo,A.voucherType
    )AS TEMP  
    WHERE TEMP.Balance>0 ;
ELSE 
        SELECT 
            TEMP.voucherNo +'_'+ TEMP.voucherType ID,
            TEMP.voucherType,
            CASE WHEN TEMP.voucherType = 'Payment Voucher' THEN 
            (SELECT paymentMasterId FROM tbl_PaymentMaster WHERE (paymentMasterId = TEMP.voucherNo))
             ELSE
            (SELECT salesInvoiceNo FROM tbl_SalesMaster WHERE (salesMasterId = TEMP.voucherNo)) END AS voucherNo,
            CAST(CAST(TEMP.balance AS DECIMAL(24,2))AS char(27)) AS amount
            FROM(
                SELECT 
                A.voucherNo,
                A.voucherType,  
                (SUM(ifnull(A.debit, 0)) - SUM(ifnull(A.credit,0))) AS balance           

                FROM tbl_PartyBalance AS A 
                WHERE (A.voucherType = 'Sales Invoice' OR A.voucherType = 'Payment Voucher'OR A.voucherType = 'Job Invoice') AND A.ledgerId=p_ledgerId AND A.branchId=p_branchId AND A.optional='False'
                GROUP BY A.voucherNo,A.voucherType
                )AS TEMP  
        WHERE TEMP.Balance > 0 ;
END

Jhunjhunwala .. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 60 — Nisar, Feb 17 '14 at 06:15
use a delimiter instead of semicolon while creating a stored procedure — Aditya Jhunjhunwala, Feb 17 '14 at 06:17

score 1 · Answer 1 · answered Feb 17 '14 at 06:20

try with this i have update some changes END IF and ; etc

CREATE PROCEDURE `PartyBalanceViewByLedgerId`

    (
    p_ledgerId varchar(50),
    p_crOrDr varchar(50),
    p_branchId varchar(50)
    )

BEGIN

IF (p_crOrDr='Dr')
    SELECT 
    TEMP.voucherNo +'_'+ TEMP.voucherType AS ID,
    TEMP.voucherType,
    CASE WHEN (TEMP.voucherType = 'Receipt Voucher')
    THEN
        (SELECT receiptMasterId FROM tbl_ReceiptMaster 
            WHERE (receiptMasterId = TEMP.voucherNo))
    ELSE
        (SELECT purchaseMasterId FROM tbl_PurchaseMaster
         WHERE (purchaseMasterId = TEMP.voucherNo)) 
    END AS voucherNo,
    CAST(CAST(TEMP.balance AS DECIMAL(24,2)) AS char(27)) 
    AS amount
    FROM(SELECT 
        A.voucherNo,
        A.voucherType,
        (SUM(ifnull(A.credit, 0)) - SUM(ifnull(A.debit, 0)))
         AS balance 
        FROM tbl_PartyBalance AS A 
        WHERE (A.voucherType = 'Purchase Invoice' 
            OR A.voucherType = 'Receipt Voucher') 
        AND A.ledgerId=p_ledgerId 
        AND A.branchId=p_branchId 
        AND A.optional='False'
        GROUP BY A.voucherNo,A.voucherType
    )AS TEMP  
    WHERE TEMP.Balance>0;

ELSE 
        SELECT 
            TEMP.voucherNo +'_'+ TEMP.voucherType ID,
            TEMP.voucherType,
            CASE WHEN TEMP.voucherType = 'Payment Voucher' THEN 
            (SELECT paymentMasterId FROM tbl_PaymentMaster WHERE (paymentMasterId = TEMP.voucherNo))
             ELSE
            (SELECT salesInvoiceNo FROM tbl_SalesMaster WHERE (salesMasterId = TEMP.voucherNo)) END AS voucherNo,
            CAST(CAST(TEMP.balance AS DECIMAL(24,2))AS char(27)) AS amount
            FROM(
                SELECT 
                A.voucherNo,
                A.voucherType,  
                (SUM(ifnull(A.debit, 0)) - SUM(ifnull(A.credit,0))) AS balance           

                FROM tbl_PartyBalance AS A 
                WHERE (A.voucherType = 'Sales Invoice' OR A.voucherType = 'Payment Voucher'OR A.voucherType = 'Job Invoice') AND A.ledgerId=p_ledgerId AND A.branchId=p_branchId AND A.optional='False'
                GROUP BY A.voucherNo,A.voucherType
                )AS TEMP  
        WHERE TEMP.Balance > 0 ;
END IF;
END;

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

Are you using a DELIMITER? If not pl go through this description:

Delimiters in MySQL

I hope this will solve your problem.

edited May 23 '17 at 11:49

Community

1
1

answered Feb 17 '14 at 06:16

Neels

2,547
6
33
40

yeah,, i am running the code with delimiter.. but no help – Nisar Feb 17 '14 at 06:17
2

Ok..figured out there is no 'END IF' block in your code. Pl add it before the last 'END'. – Neels Feb 17 '14 at 06:22

score 0 · Answer 3 · answered Feb 17 '14 at 06:18

0

Remove the semicolon before ELSE in :

GROUP BY A.voucherNo,A.voucherType
)AS TEMP  
WHERE TEMP.Balance>0 
ELSE 
    SELECT 
        TEMP.voucherNo +'_'+ TEMP.voucherType ID,
        TEMP.voucherType,

answered Feb 17 '14 at 06:18

Aditya Jhunjhunwala

635
6
19

Error in last line near end

3 Answers3