TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
anil john
NA
156
209.7k
Incorrect syntax near the keyword 'if'
May 7 2013 6:46 AM
Error:-
Incorrect syntax near the keyword 'if'
Sql Code:-
Alter Proc [dbo].[PRC_ACC_ACCOUNT_TRANS_INSERT]
@Report varchar(50)
as
BEGIN
-- Clear all the records from Account Trans
TRUNCATE TABLE tbl_Acc_AccountTrans
insert into tbl_Acc_AccountTrans
(
iAcID,iAcGrpid,iSlNo,iVoucherNo,vSeries,dtVoucherDate
,nvVoucherType,nvFormType,dcDRAmount,dcCRAmount,nvNarration
,iFinId,iBranchId,iCoId
)
-----------0 Get Opening Balance------------------------------------------
--select * from tbl_Acc_AccountChild
--select Feilds from Transaction tables
if @Report='CASH'
--begin
select
(select iAcID from tbl_Acc_AccountMaster Where vCategory='CASH BOOK')iAcID,
(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tblSales.iAcID)iAcGroup,
iSalesID,SerialNo as Voucher,
(select Series from dbo.tbl_VoucherConfig where Series=tblSales.Series)as vSeries,
dtDate,Series,'Sales'as FormType,
Total as deDRAmount,0 as deCRAmount,'Narration' as vNarration
,iFinId,iBranchId,iCoId
from tblSales
--end
Union All
select
iAcID
,(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tbl_Acc_AccountChild.iAcID)iAcGroup
,1,1 as Voucher
,'OP'as vSeries
,getdate(),'OP' VoucherType,'A/c Master'as FormType
,deOPDRBal,deOPCRBal,'Opening balance'
,iFinId,iBranchId,iCoId
from tbl_Acc_AccountChild
union all
------------tbl_Sales-------------------------------------------------
select
iAcID,
(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tblSales.iAcID)iAcGroup,
iSalesID,SerialNo as Voucher,
(select Series from dbo.tbl_VoucherConfig where Series=tblSales.Series)as vSeries,
dtDate,Series,'Sales'as FormType,
Total as deDRAmount,0 as deCRAmount,'Narration' as vNarration
,iFinId,iBranchId,iCoId
from tblSales
--union all
--if @Report='CASH'
--begin
--select
--(select iAcID from tbl_Acc_AccountMaster Where vCategory='CASH BOOK')iAcID,
--(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tblSales.iAcID)iAcGroup,
--iSalesID,SerialNo as Voucher,
--(select Series from dbo.tbl_VoucherConfig where Series=tblSales.Series)as vSeries,
--dtDate,Series,'Sales'as FormType,
--Total as deDRAmount,0 as deCRAmount,'Narration' as vNarration
--,iFinId,iBranchId,iCoId
--
--from tblSales
--end
union all
-----------1 tbl_Acc_Voucher------------------------------------------
select
iAcID
,(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tbl_Acc_Voucher.iAcID)iAcGroup
,RPID,iRPNO as Voucher
,(select Series from dbo.tbl_VoucherConfig where SeriesID=tbl_Acc_Voucher.iSeriesID)as vSeries
,dRPDate,cVType VoucherType,'AcVoucher'as FormType
,deDRAmount,deCRAmount,vNarration
,iFinId,iBranchId,iCoId
from tbl_Acc_Voucher
union all
-----------2 tbl_Acc_DrCrNote------------------------------------------
select
iAcID
,(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tbl_Acc_DrCrNote.iAcID)
,NID,iNoteNo as Voucher,vSeries,dNoteDate,cVType VoucherType
,'AcDrCrNote'as FormType,deDRAmount,deCRAmount,vNarration
,iFinId,iBranchId,iCoId
from tbl_Acc_DrCrNote
union all
-----------3 tbl_Acc_Contra------------------------------------------
select
iAcID
,(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tbl_Acc_Contra.iAcID)
,CID,iCNo as Voucher
,(select Series from dbo.tbl_VoucherConfig where SeriesID=tbl_Acc_Contra.iSeriesID)as vSeries
,dCNdate,'CO' VoucherType,'AcVoucher'as FormType
,deDRAmount,deCRAmount,vNarration,iFinId,iBranchId,iCoId
from tbl_Acc_Contra
union all
-----------4 tbl_Acc_Journal------------------------------------------
select
iAcID
,(select iAcGroup from tbl_Acc_AccountMaster where iAcID=tbl_Acc_Journal.iAcID)
,JID,vJNO as Voucher
,(select Series from dbo.tbl_VoucherConfig where SeriesID=tbl_Acc_Journal.iSeriesID)as vSeries
,djdate,'JE' VoucherType,'AcJournal'as FormType
,deDRAmount,deCRAmount,vNarration,iFinId,iBranchId,iCoId
from tbl_Acc_Journal
union all
-----------5 tblChitReceipts------------------------------------------
select
iAcId
,isnull((select iAcGroup from tbl_Acc_AccountMaster where iAcID=tblChitReceipts.iAcID),0)
,iRid,vSlipNo as Voucher
,(select Series from dbo.tbl_VoucherConfig where SeriesID=tblChitReceipts.iSeriesID)as vSeries
,convert(datetime,convert(varchar,dTrDt,101)) as dTrDt
,'CR'VoucherType,'ChitReceipts'as FormType
,0 as deDRAmount,deCRAmount as deCRAmount,'Cash Receipt' as Narration
,iFinId,iBranchId,iCoId
from tblChitReceipts
union all
select
nAccCashBankCode as iAcId
,isnull((select iAcGroup from tbl_Acc_AccountMaster where iAcID=tblChitReceipts.nAccCashBankCode),0)
,iRid,vSlipNo as Voucher
,(select Series from dbo.tbl_VoucherConfig where SeriesID=tblChitReceipts.iSeriesID)as vSeries
,convert(datetime,convert(varchar,dTrDt,101)) as dTrDt
,'CR'VoucherType,'ChitReceipts'as FormType
, deCRAmount as deDRAmount,0 as deCRAmount,'Cash Receipt' as Narration
,iFinId,iBranchId,iCoId
from tblChitReceipts
union all
-----------6 tblChit Payment------------------------------------------
select
iAcId
,(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tblChittyPayments.iAcID)
,iPId,vVoucherNo as Voucher
,(select Series from dbo.tbl_VoucherConfig where SeriesID=tblChittyPayments.iSeriesID)as vSeries
,convert(datetime,convert(varchar,dTrDt,101)) as dTrDt
,'CP'VoucherType,'ChitPayments'as FormType
,0 as deDRAmount,deCRAmount as deCRAmount,'Cash Payment' as Narration
,iFinId,iBranchId,iCoId
from tblChittyPayments
union all
select
nAccCashBankCode as iAcId
,(select isnull(iAcGroup,0) from tbl_Acc_AccountMaster where iAcID=tblChittyPayments.nAccCashBankCode)
,iPId,vVoucherNo as Voucher
,(select Series from dbo.tbl_VoucherConfig where SeriesID=tblChittyPayments.iSeriesID)as vSeries
,convert(datetime,convert(varchar,dTrDt,101)) as dTrDt
,'CP'VoucherType,'ChitPayments'as FormType
,0 as deDRAmount,deCRAmount as deCRAmount,'Cash Payment' as Narration
,iFinId,iBranchId,iCoId
from tblChittyPayments
update tbl_Acc_AccountTrans
set iRootId=ISNULL((select iRootID from dbo.tbl_Acc_AccountGroup where iGroupID=tbl_Acc_AccountTrans.iAcGrpid),0)
,iParentId=ISNULL((select iParentID from dbo.tbl_Acc_AccountGroup where iGroupID=tbl_Acc_AccountTrans.iAcGrpid),0)
END
Reply
Answers (
2
)
Split string and loop through against a table
solve concurrency if multiple users update the same record a