I have a tables bankcheques,cheques and chequedetails now i create the procedure for
if bank_id,bankchequeno doesn't exist in bankcheques table insert record.while updating the record i am checking two conditions if chequeno not exists in chequedetails table update every field from the bankcheques table.
and if chequeno exists in chequedetails table only update the status field in the bankcheques table. here iam checking another condition.
i am taking maxcheueno from cheques table based on bankid and chequeend from bankcheques table and compare the both values then update status of bankcheque table.I am getting the incorrect syntax near '=' Error Please help me on this.Thanks in Advance.
What I have tried:
USE [ChequeManager]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Insertupdatebankcheques_iu]
(@bank_Id int
,@bankslno int
,@bankChqstartno nvarchar(6)
,@bankChqlvscount smallint
,@bankChqendno nvarchar(6)
,@bank_Stat bit
,@InsUpdt bit)
as
begin
declare @lastcheueno as int
declare @chqlastno as int
select @lastcheueno= max([ChequeNo]) FROM [ChequeManager].[dbo].[Cheques] where [bank_Id]=@bank_Id
SELECT @chqlastno= [bank_Chqendno] FROM [ChequeManager].[dbo].[bankcheques] where bank_Id=@bank_Id and bank_Slno=@bankslno
if(@InsUpdt=1)
begin
if not exists(select bank_Id,bank_Chqstartno
FROM [ChequeManager].[dbo].[bankcheques]
where bank_Id=@bank_Id)
begin
INSERT INTO [ChequeManager].[dbo].[bankcheques]
(bank_Id,
bank_Slno,
[bank_Chqstartno]
,[bank_Chqlvscount]
,[bank_Chqendno]
,bank_Stat)
values( @bank_Id,
@bankslno
,@bankChqstartno
,@bankChqlvscount
,@bankChqendno,
@bank_Stat)
end
end
if(@InsUpdt=0)
begin
if not exists(select chqs_Chequeno
FROM [ChequeManager].[dbo].[chequedetails]
where bank_Id=@bank_Id)
begin
UPDATE [ChequeManager].[dbo].[bankcheques]
set bank_Chqstartno=@bankChqstartno,
bank_Chqlvscount=@bankChqlvscount,
bank_Chqendno=@bankChqendno,
[bank_Stat]=@bank_Stat
,[bank_Mstmp] = getdate()
WHERE bank_Id=@bank_Id and [bank_Slno]=@bankslno
end
end
if exists(select chqs_Chequeno FROM [ChequeManager].[dbo].[chequedetails] where bank_Id=@bank_Id)
begin
if(@chqlastno==@lastcheueno)
begin
UPDATE [ChequeManager].[dbo].[bankcheques]
set [bank_Stat]=@bank_Stat
,[bank_Mstmp] = getdate()
WHERE bank_Id=@bank_Id and [bank_Slno]=@bankslno
end
end
end