About Lesson
USE [sircldb]
GO
/****** Object: UserDefinedFunction [dbo].[fn_getbillsrno] Script Date: 10/09/2024 8:12:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[fn_getbillsrno](@bno int) returns int
as
begin
declare @sno int=0;
select @sno= isnull(max(srno),0)+1 from tblBillDetail where BillNo=@bno;
return @sno;
end
alter procedure sp_createbill(
@item nvarchar(50),
@rate decimal(18,2),
@qty decimal(18,2),
@gst decimal(18,2),
@bno int=0
)
as
begin
declare @amtsum decimal(18,2);
declare @gstsum decimal(18,2);
declare @netsum decimal(18,2);
–if @bno is 0 then we assume we add an item after created a new bill
–if @bno is not 0 then we assume we want to add an item into existing bill
–first of all create a dummy entry in billmaster with 0 values
–from this action billno will be generated for us that will be used in billdetail table as FK
if @bno=0
begin
insert into tblBillMaster values(getdate(),0,0,0,0,0);
— now we newly insrted billno required for billdetails so we need to get it from tblbillmaster
–after insert so query is givel below
select @bno=isnull(max(billno),1) from tblBillMaster;
end
insert into tblBillDetail values(
@bno,
dbo.fn_getbillsrno(@bno),
@item,@rate,@qty,@gst
);
— after insert into bill detail we need to update the bill master with sumation of billdetail
select
@amtsum=isnull(sum(amount),0),
@gstsum=isnull(sum(GSTAmount),0),
@netsum=isnull(sum(NetAmount),0)
from tblBillDetail where BillNo=@bno;
— now update bill master
update tblBillMaster
set Amount=@amtsum, TaxableAmount=@gstsum,NetAmount=@netsum
where BillNo=@bno;
end;
execute sp_createbill ‘Sticker’,1,100,0,1
select * from tblBillMaster;
select * from tblBillDetail;