USE [SMS]
GO
/******
Object: StoredProcedure
[dbo].[SP_StudentInvoice] Script Date:
02/19/2013 11:19:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
=============================================
-- Author: <ShihaFath Wazeer>
-- Create date:
<19-Feb-2013>
-- Description: <Description,,>
--
=============================================
Create PROCEDURE [dbo].[SP_StudentInvoice]
-- Add the
parameters for the stored procedure here
@GradeID INT,
@Year INT,
AS
BEGIN
-- Declare temporary table
DECLARE
@Students TABLE
(
SeqNo INT NOT NULL,
AdmissionNumber varchar(10) NOT NULL,
Name1 varchar(100) NULL,
Name2 varchar(100) NULL,
GradeID INT NOT NULL,
Section INT NOT NULL,
SectionName varchar(20) NULL,
GradeName varchar(50) NULL,
GradeFee Money NULL,
SpecialDiscount Money NULL,
January varchar(10) NULL,
February varchar(10) NULL,
March varchar(10) NULL,
April varchar(10) NULL,
May varchar(10) NULL,
June varchar(10)
NULL,
July varchar(10)
NULL,
August varchar(10) NULL,
September varchar(10) NULL,
October varchar(10) NULL,
November varchar(10) NULL,
December varchar(10) NULL
);
-- Insert all
students’ details to temporary table
INSERT @Students
SELECT
s.SeqNo,
s.AdmissionNumber,
s.Name1, ISNULL(s.Name2, '') AS Name2,
s.GradeID,
s.Section,
sec.SectionName,
g.GradeName,
g.Fees
as GradeFee,
isnull(s.discount,0) as SpecialDiscount,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
FROM tbl_Student AS
s INNER JOIN
tbl_Grade AS g ON s.GradeID = g.GradeID INNER JOIN
tbl_Section AS sec ON s.Section = sec.SectionID
WHERE (s.Status = 1) AND (s.isFullDiscount = 0) AND (s.GradeID = @GradeID);
-------------------------------------------------------------
-- Variable
Declaration
DECLARE @SeqNo INT
DECLARE
@AdmissionNumber VARCHAR(10)
DECLARE @GradeFee Money
DECLARE
@SpecialDiscount Money
DECLARE @DueStatus
VARCHAR(5)
-- Cursor
Declaration
DECLARE
db_Studentcursor CURSOR FOR
-- Query For Cursor
SELECT DISTINCT
SeqNo,
AdmissionNumber,
GradeFee,
SpecialDiscount
FROM @Students
--Open Cursor
OPEN
db_Studentcursor
-- Go to Next
Recored
FETCH NEXT FROM
db_Studentcursor INTO @SeqNo,@AdmissionNumber,@GradeFee,@SpecialDiscount
WHILE @@FETCH_STATUS = 0
BEGIN
--update @Students
--set
--January
= -- Some Logic within student - Seq Number
--where
SeqNo = @SeqNo
--Logic
FETCH NEXT FROM
db_Studentcursor INTO @SeqNo,@AdmissionNumber,@GradeFee,@SpecialDiscount
END
-- Close Cursor
CLOSE
db_Studentcursor
DEALLOCATE
db_Studentcursor
-------------------------------------------------------------
--Return Final
Table
select * from @Students order
by AdmissionNumber
END
