Friday, March 9, 2012

Is it not possible to nest IFs in a stored procedure

I have been working on this stored porcedure and now that I have it ready I get an error:

Msg 156, Level 15, State 1, Procedure UpdateFundAllocation, Line 38

Incorrect syntax near the keyword 'ELSE'.

Is it not possible to nest ifs the way I have them?

I greatly appreciate your help.

@.Receipt_IDInt,@.PRFund_IDInt,@.Fund_IDint,@.Amount_allocatedmoney,@.LastUpdatedatetime,@.LastUpdateBynvarchar(50)ASDeclare @.row_countintDeclare @.total_allocationsdecimal(18,2)Declare @.total_paymentsdecimal(18,2)SELECT @.row_count =COUNT(tblPayReceiptsFunds.Receipt_ID)FROMtblPayReceiptsFundsINNERJOIN tblPayReceiptsON tblPayReceiptsFunds.Receipt_ID = tblPayReceipts.Receipt_IDWHERE tblPayReceiptsFunds.Receipt_ID=@.Receipt_IDIF (@.row_count = 1)SELECT @.total_allocations = @.Amount_allocated, @.total_payments =Sum(tblPayReceipts.AmountPaid)FROM tblPayReceiptsFundsINNERJOIN tblPayReceiptsON tblPayReceiptsFunds.Receipt_ID = tblPayReceipts.Receipt_IDWHERE tblPayReceipts.Receipt_ID=@.Receipt_IDUPDATE tblPayReceiptsFundsSET [Fund_ID]=@.Fund_ID,[Amount_ALLOCATED]=@.Amount_ALLOCATED,LastUpdate=@.LastUpdate,LastUpdateBy=@.LastUpdateByWHERE [PRFund_ID]=@.PRFund_IDSELECT'Fund was allocated.'AS MESSAGEELSE BEGINSELECT @.total_allocations = (SUM(tblPayReceiptsFunds.Amount_allocated + @.Amount_allocated)), @.total_payments =Sum(tblPayReceipts.AmountPaid)FROM tblPayReceiptsFundsINNERJOIN tblPayReceiptsON tblPayReceiptsFunds.Receipt_ID = tblPayReceipts.Receipt_IDWHERE tblPayReceipts.Receipt_ID=@.Receipt_IDIF (@.total_allocations > @.total_payments)SELECT'You are attempting to allocate more to funds than your total payment.'AS MESSAGEELSE BEGINUPDATE tblPayReceiptsFundsSET [Fund_ID]=@.Fund_ID,[Amount_ALLOCATED]=@.Amount_ALLOCATED,LastUpdate=@.LastUpdate,LastUpdateBy=@.LastUpdateByWHERE [PRFund_ID]=@.PRFund_IDSELECT'Fund was allocated.'AS MESSAGEENDEND

Add a BEGIN and END to delineate the IF portions too

IF BEGIN--somethingENDELSE BEGIN IF BEGIN--somethingEND ELSE BEGIN--somethingEND END
|||

You probably missed BEGIN after first IF clasue and END before First ELSE. Rememeber you have to have single block of code between IF and ELSE if you case you have SELECT, UPDATE and SELECT so you have to put BEGIN/END around thet to make them be a single block.

No comments:

Post a Comment