USE SmartInstitute
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
-- Drop the dbo.prc_Account_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_Get_List
GO
-- Drop the dbo.prc_Account_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_GetPaged
GO
-- Drop the dbo.prc_Account_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_Insert
GO
-- Drop the dbo.prc_Account_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_Update
GO
-- Drop the dbo.prc_Account_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_Delete
GO
-- Drop the dbo.prc_Account_GetByStudentID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_GetByStudentID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_GetByStudentID
GO
-- Drop the dbo.prc_Account_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Account_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Account_GetByID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Account table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Account_Get_List
AS
SELECT
[ID],
[StudentID],
[TransactionDescription],
[TransactionDate],
[DebitAmount],
[CreditAmount],
[Balance],
[ChangeStamp],
[Type],
[BankName],
[CheckNumber],
[ORNumber]
FROM
dbo.[Account]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Account table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Account_GetPaged
(
@WhereClause varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Account]'
IF @WhereClause IS NOT NULL
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF @OrderBy IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return total count
SELECT @@ROWCOUNT
--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn
-- Return paged results
SELECT O.ID, O.StudentID, O.TransactionDescription, O.TransactionDate, O.DebitAmount, O.CreditAmount, O.Balance, O.ChangeStamp, O.Type, O.BankName, O.CheckNumber, O.ORNumber
FROM
dbo.[Account] O,
#PageIndex PageIndex
WHERE
O.ID = PageIndex.ID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Account table and returns the new PK
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Account_Insert
@Balance money,
@BankName varchar(255),
@ChangeStamp smalldatetime,
@CheckNumber varchar(255),
@CreditAmount money,
@DebitAmount money,
@ORNumber varchar(255),
@StudentID int,
@TransactionDate datetime,
@TransactionDescription varchar(6000),
@Type int
AS
declare @new_id int
INSERT INTO dbo.[Account] (
[Balance],
[BankName],
[ChangeStamp],
[CheckNumber],
[CreditAmount],
[DebitAmount],
[ORNumber],
[StudentID],
[TransactionDate],
[TransactionDescription],
[Type]
) VALUES (
@Balance,
@BankName,
@ChangeStamp,
@CheckNumber,
@CreditAmount,
@DebitAmount,
@ORNumber,
@StudentID,
@TransactionDate,
@TransactionDescription,
@Type
)
set @new_id = SCOPE_IDENTITY()
SELECT
[ID],
[StudentID],
[TransactionDescription],
[TransactionDate],
[DebitAmount],
[CreditAmount],
[Balance],
[ChangeStamp],
[Type],
[BankName],
[CheckNumber],
[ORNumber]
FROM dbo.[Account]
WHERE
[ID] = @new_id
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Account table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Account_Update
@ID int,
@Balance money,
@BankName varchar(255),
@ChangeStamp smalldatetime,
@CheckNumber varchar(255),
@CreditAmount money,
@DebitAmount money,
@ORNumber varchar(255),
@StudentID int,
@TransactionDate datetime,
@TransactionDescription varchar(6000),
@Type int
AS
UPDATE dbo.[Account]
SET
[Balance] = @Balance,
[BankName] = @BankName,
[ChangeStamp] = @ChangeStamp,
[CheckNumber] = @CheckNumber,
[CreditAmount] = @CreditAmount,
[DebitAmount] = @DebitAmount,
[ORNumber] = @ORNumber,
[StudentID] = @StudentID,
[TransactionDate] = @TransactionDate,
[TransactionDescription] = @TransactionDescription,
[Type] = @Type
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
SELECT
[ID],
[StudentID],
[TransactionDescription],
[TransactionDate],
[DebitAmount],
[CreditAmount],
[Balance],
[ChangeStamp],
[Type],
[BankName],
[CheckNumber],
[ORNumber]
FROM dbo.[Account]
WHERE
[ID] = @ID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Account table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Account_Delete
@ID int
,
@ChangeStamp smalldatetime
AS
DELETE FROM dbo.[Account]
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Account table through a foreign key
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Account_GetByStudentID
@StudentID int
AS
SELECT
[ID],
[StudentID],
[TransactionDescription],
[TransactionDate],
[DebitAmount],
[CreditAmount],
[Balance],
[ChangeStamp],
[Type],
[BankName],
[CheckNumber],
[ORNumber]
FROM
dbo.[Account]
WHERE
[StudentID] = @StudentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Account table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Account_GetByID
@ID int
AS
SELECT
[ID],
[StudentID],
[TransactionDescription],
[TransactionDate],
[DebitAmount],
[CreditAmount],
[Balance],
[ChangeStamp],
[Type],
[BankName],
[CheckNumber],
[ORNumber]
FROM
[dbo].[Account]
WHERE
[ID] = @ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- Drop the dbo.prc_Assessment_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_Get_List
GO
-- Drop the dbo.prc_Assessment_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_GetPaged
GO
-- Drop the dbo.prc_Assessment_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_Insert
GO
-- Drop the dbo.prc_Assessment_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_Update
GO
-- Drop the dbo.prc_Assessment_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_Delete
GO
-- Drop the dbo.prc_Assessment_GetByStudentID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_GetByStudentID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_GetByStudentID
GO
-- Drop the dbo.prc_Assessment_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Assessment_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Assessment_GetByID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Assessment table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Assessment_Get_List
AS
SELECT
[ID],
[Serial],
[StudentID],
[ExamPermitNumber],
[AdmissionFee],
[TuitionFee],
[ComputerLabFee],
[ScienceLabFee],
[ActivityFee],
[DevelopmentFee],
[MiscFee],
[MedicalFee],
[LibraryFee],
[IDCardFee],
[OtherFee1],
[OtherFee1Desc],
[OtherFee2],
[OtherFee2Desc],
[Discount],
[Scholarship],
[OtherDiscount],
[OtherDiscountDesc],
[RegistrationDate],
[IsAdmissionFeePaid],
[IsAnnualFeePaid],
[PaymentType],
[ScholarshipInPercent],
[DiscountInPercent],
[Currency],
[SemesterOrder],
[PreviousBalance],
[ChangeStamp]
FROM
dbo.[Assessment]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Assessment table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Assessment_GetPaged
(
@WhereClause varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Assessment]'
IF @WhereClause IS NOT NULL
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF @OrderBy IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return total count
SELECT @@ROWCOUNT
--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn
-- Return paged results
SELECT O.ID, O.Serial, O.StudentID, O.ExamPermitNumber, O.AdmissionFee, O.TuitionFee, O.ComputerLabFee, O.ScienceLabFee, O.ActivityFee, O.DevelopmentFee, O.MiscFee, O.MedicalFee, O.LibraryFee, O.IDCardFee, O.OtherFee1, O.OtherFee1Desc, O.OtherFee2, O.OtherFee2Desc, O.Discount, O.Scholarship, O.OtherDiscount, O.OtherDiscountDesc, O.RegistrationDate, O.IsAdmissionFeePaid, O.IsAnnualFeePaid, O.PaymentType, O.ScholarshipInPercent, O.DiscountInPercent, O.Currency, O.SemesterOrder, O.PreviousBalance, O.ChangeStamp
FROM
dbo.[Assessment] O,
#PageIndex PageIndex
WHERE
O.ID = PageIndex.ID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Assessment table and returns the new PK
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Assessment_Insert
@ActivityFee money,
@AdmissionFee money,
@ChangeStamp smalldatetime,
@ComputerLabFee money,
@Currency int,
@DevelopmentFee money,
@Discount money,
@DiscountInPercent int,
@ExamPermitNumber varchar(10),
@IDCardFee money,
@IsAdmissionFeePaid bit,
@IsAnnualFeePaid bit,
@LibraryFee money,
@MedicalFee money,
@MiscFee money,
@OtherDiscount money,
@OtherDiscountDesc varchar(255),
@OtherFee1 money,
@OtherFee1Desc varchar(255),
@OtherFee2 money,
@OtherFee2Desc varchar(255),
@PaymentType int,
@PreviousBalance money,
@RegistrationDate datetime,
@Scholarship money,
@ScholarshipInPercent int,
@ScienceLabFee money,
@SemesterOrder int,
@Serial int,
@StudentID int,
@TuitionFee money
AS
declare @new_id int
INSERT INTO dbo.[Assessment] (
[ActivityFee],
[AdmissionFee],
[ChangeStamp],
[ComputerLabFee],
[Currency],
[DevelopmentFee],
[Discount],
[DiscountInPercent],
[ExamPermitNumber],
[IDCardFee],
[IsAdmissionFeePaid],
[IsAnnualFeePaid],
[LibraryFee],
[MedicalFee],
[MiscFee],
[OtherDiscount],
[OtherDiscountDesc],
[OtherFee1],
[OtherFee1Desc],
[OtherFee2],
[OtherFee2Desc],
[PaymentType],
[PreviousBalance],
[RegistrationDate],
[Scholarship],
[ScholarshipInPercent],
[ScienceLabFee],
[SemesterOrder],
[Serial],
[StudentID],
[TuitionFee]
) VALUES (
@ActivityFee,
@AdmissionFee,
@ChangeStamp,
@ComputerLabFee,
@Currency,
@DevelopmentFee,
@Discount,
@DiscountInPercent,
@ExamPermitNumber,
@IDCardFee,
@IsAdmissionFeePaid,
@IsAnnualFeePaid,
@LibraryFee,
@MedicalFee,
@MiscFee,
@OtherDiscount,
@OtherDiscountDesc,
@OtherFee1,
@OtherFee1Desc,
@OtherFee2,
@OtherFee2Desc,
@PaymentType,
@PreviousBalance,
@RegistrationDate,
@Scholarship,
@ScholarshipInPercent,
@ScienceLabFee,
@SemesterOrder,
@Serial,
@StudentID,
@TuitionFee
)
set @new_id = SCOPE_IDENTITY()
SELECT
[ID],
[Serial],
[StudentID],
[ExamPermitNumber],
[AdmissionFee],
[TuitionFee],
[ComputerLabFee],
[ScienceLabFee],
[ActivityFee],
[DevelopmentFee],
[MiscFee],
[MedicalFee],
[LibraryFee],
[IDCardFee],
[OtherFee1],
[OtherFee1Desc],
[OtherFee2],
[OtherFee2Desc],
[Discount],
[Scholarship],
[OtherDiscount],
[OtherDiscountDesc],
[RegistrationDate],
[IsAdmissionFeePaid],
[IsAnnualFeePaid],
[PaymentType],
[ScholarshipInPercent],
[DiscountInPercent],
[Currency],
[SemesterOrder],
[PreviousBalance],
[ChangeStamp]
FROM dbo.[Assessment]
WHERE
[ID] = @new_id
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Assessment table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Assessment_Update
@ID int,
@ActivityFee money,
@AdmissionFee money,
@ChangeStamp smalldatetime,
@ComputerLabFee money,
@Currency int,
@DevelopmentFee money,
@Discount money,
@DiscountInPercent int,
@ExamPermitNumber varchar(10),
@IDCardFee money,
@IsAdmissionFeePaid bit,
@IsAnnualFeePaid bit,
@LibraryFee money,
@MedicalFee money,
@MiscFee money,
@OtherDiscount money,
@OtherDiscountDesc varchar(255),
@OtherFee1 money,
@OtherFee1Desc varchar(255),
@OtherFee2 money,
@OtherFee2Desc varchar(255),
@PaymentType int,
@PreviousBalance money,
@RegistrationDate datetime,
@Scholarship money,
@ScholarshipInPercent int,
@ScienceLabFee money,
@SemesterOrder int,
@Serial int,
@StudentID int,
@TuitionFee money
AS
UPDATE dbo.[Assessment]
SET
[ActivityFee] = @ActivityFee,
[AdmissionFee] = @AdmissionFee,
[ChangeStamp] = @ChangeStamp,
[ComputerLabFee] = @ComputerLabFee,
[Currency] = @Currency,
[DevelopmentFee] = @DevelopmentFee,
[Discount] = @Discount,
[DiscountInPercent] = @DiscountInPercent,
[ExamPermitNumber] = @ExamPermitNumber,
[IDCardFee] = @IDCardFee,
[IsAdmissionFeePaid] = @IsAdmissionFeePaid,
[IsAnnualFeePaid] = @IsAnnualFeePaid,
[LibraryFee] = @LibraryFee,
[MedicalFee] = @MedicalFee,
[MiscFee] = @MiscFee,
[OtherDiscount] = @OtherDiscount,
[OtherDiscountDesc] = @OtherDiscountDesc,
[OtherFee1] = @OtherFee1,
[OtherFee1Desc] = @OtherFee1Desc,
[OtherFee2] = @OtherFee2,
[OtherFee2Desc] = @OtherFee2Desc,
[PaymentType] = @PaymentType,
[PreviousBalance] = @PreviousBalance,
[RegistrationDate] = @RegistrationDate,
[Scholarship] = @Scholarship,
[ScholarshipInPercent] = @ScholarshipInPercent,
[ScienceLabFee] = @ScienceLabFee,
[SemesterOrder] = @SemesterOrder,
[Serial] = @Serial,
[StudentID] = @StudentID,
[TuitionFee] = @TuitionFee
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
SELECT
[ID],
[Serial],
[StudentID],
[ExamPermitNumber],
[AdmissionFee],
[TuitionFee],
[ComputerLabFee],
[ScienceLabFee],
[ActivityFee],
[DevelopmentFee],
[MiscFee],
[MedicalFee],
[LibraryFee],
[IDCardFee],
[OtherFee1],
[OtherFee1Desc],
[OtherFee2],
[OtherFee2Desc],
[Discount],
[Scholarship],
[OtherDiscount],
[OtherDiscountDesc],
[RegistrationDate],
[IsAdmissionFeePaid],
[IsAnnualFeePaid],
[PaymentType],
[ScholarshipInPercent],
[DiscountInPercent],
[Currency],
[SemesterOrder],
[PreviousBalance],
[ChangeStamp]
FROM dbo.[Assessment]
WHERE
[ID] = @ID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Assessment table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Assessment_Delete
@ID int
,
@ChangeStamp smalldatetime
AS
DELETE FROM dbo.[Assessment]
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Assessment table through a foreign key
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Assessment_GetByStudentID
@StudentID int
AS
SELECT
[ID],
[Serial],
[StudentID],
[ExamPermitNumber],
[AdmissionFee],
[TuitionFee],
[ComputerLabFee],
[ScienceLabFee],
[ActivityFee],
[DevelopmentFee],
[MiscFee],
[MedicalFee],
[LibraryFee],
[IDCardFee],
[OtherFee1],
[OtherFee1Desc],
[OtherFee2],
[OtherFee2Desc],
[Discount],
[Scholarship],
[OtherDiscount],
[OtherDiscountDesc],
[RegistrationDate],
[IsAdmissionFeePaid],
[IsAnnualFeePaid],
[PaymentType],
[ScholarshipInPercent],
[DiscountInPercent],
[Currency],
[SemesterOrder],
[PreviousBalance],
[ChangeStamp]
FROM
dbo.[Assessment]
WHERE
[StudentID] = @StudentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Assessment table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Assessment_GetByID
@ID int
AS
SELECT
[ID],
[Serial],
[StudentID],
[ExamPermitNumber],
[AdmissionFee],
[TuitionFee],
[ComputerLabFee],
[ScienceLabFee],
[ActivityFee],
[DevelopmentFee],
[MiscFee],
[MedicalFee],
[LibraryFee],
[IDCardFee],
[OtherFee1],
[OtherFee1Desc],
[OtherFee2],
[OtherFee2Desc],
[Discount],
[Scholarship],
[OtherDiscount],
[OtherDiscountDesc],
[RegistrationDate],
[IsAdmissionFeePaid],
[IsAnnualFeePaid],
[PaymentType],
[ScholarshipInPercent],
[DiscountInPercent],
[Currency],
[SemesterOrder],
[PreviousBalance],
[ChangeStamp]
FROM
[dbo].[Assessment]
WHERE
[ID] = @ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- Drop the dbo.prc_Course_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_Get_List
GO
-- Drop the dbo.prc_Course_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_GetPaged
GO
-- Drop the dbo.prc_Course_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_Insert
GO
-- Drop the dbo.prc_Course_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_Update
GO
-- Drop the dbo.prc_Course_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_Delete
GO
-- Drop the dbo.prc_Course_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Course_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Course_GetByID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Course table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Course_Get_List
AS
SELECT
[ID],
[CourseCode],
[Title],
[SciCredit],
[CompCredit],
[LecCredit],
[ChangeStamp]
FROM
dbo.[Course]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Course table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Course_GetPaged
(
@WhereClause varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Course]'
IF @WhereClause IS NOT NULL
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF @OrderBy IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return total count
SELECT @@ROWCOUNT
--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn
-- Return paged results
SELECT O.ID, O.CourseCode, O.Title, O.SciCredit, O.CompCredit, O.LecCredit, O.ChangeStamp
FROM
dbo.[Course] O,
#PageIndex PageIndex
WHERE
O.ID = PageIndex.ID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Course table and returns the new PK
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Course_Insert
@ChangeStamp smalldatetime,
@CompCredit int,
@CourseCode varchar(255),
@LecCredit int,
@SciCredit int,
@Title varchar(255)
AS
declare @new_id int
INSERT INTO dbo.[Course] (
[ChangeStamp],
[CompCredit],
[CourseCode],
[LecCredit],
[SciCredit],
[Title]
) VALUES (
@ChangeStamp,
@CompCredit,
@CourseCode,
@LecCredit,
@SciCredit,
@Title
)
set @new_id = SCOPE_IDENTITY()
SELECT
[ID],
[CourseCode],
[Title],
[SciCredit],
[CompCredit],
[LecCredit],
[ChangeStamp]
FROM dbo.[Course]
WHERE
[ID] = @new_id
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Course table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Course_Update
@ID int,
@ChangeStamp smalldatetime,
@CompCredit int,
@CourseCode varchar(255),
@LecCredit int,
@SciCredit int,
@Title varchar(255)
AS
UPDATE dbo.[Course]
SET
[ChangeStamp] = @ChangeStamp,
[CompCredit] = @CompCredit,
[CourseCode] = @CourseCode,
[LecCredit] = @LecCredit,
[SciCredit] = @SciCredit,
[Title] = @Title
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
SELECT
[ID],
[CourseCode],
[Title],
[SciCredit],
[CompCredit],
[LecCredit],
[ChangeStamp]
FROM dbo.[Course]
WHERE
[ID] = @ID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Course table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Course_Delete
@ID int
,
@ChangeStamp smalldatetime
AS
DELETE FROM dbo.[Course]
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Course table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Course_GetByID
@ID int
AS
SELECT
[ID],
[CourseCode],
[Title],
[SciCredit],
[CompCredit],
[LecCredit],
[ChangeStamp]
FROM
[dbo].[Course]
WHERE
[ID] = @ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- Drop the dbo.prc_CourseSection_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_Get_List
GO
-- Drop the dbo.prc_CourseSection_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_GetPaged
GO
-- Drop the dbo.prc_CourseSection_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_Insert
GO
-- Drop the dbo.prc_CourseSection_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_Update
GO
-- Drop the dbo.prc_CourseSection_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_Delete
GO
-- Drop the dbo.prc_CourseSection_GetByCourseID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_GetByCourseID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_GetByCourseID
GO
-- Drop the dbo.prc_CourseSection_GetByCourseID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_GetByCourseID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_GetByCourseID
GO
-- Drop the dbo.prc_CourseSection_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseSection_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseSection_GetByID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the CourseSection table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseSection_Get_List
AS
SELECT
[ID],
[Title],
[Description],
[Capacity],
[CourseID],
[ClassID],
[Status],
[Type],
[ChangeStamp]
FROM
dbo.[CourseSection]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the CourseSection table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseSection_GetPaged
(
@WhereClause varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[CourseSection]'
IF @WhereClause IS NOT NULL
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF @OrderBy IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return total count
SELECT @@ROWCOUNT
--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn
-- Return paged results
SELECT O.ID, O.Title, O.Description, O.Capacity, O.CourseID, O.ClassID, O.Status, O.Type, O.ChangeStamp
FROM
dbo.[CourseSection] O,
#PageIndex PageIndex
WHERE
O.ID = PageIndex.ID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the CourseSection table and returns the new PK
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseSection_Insert
@Capacity int,
@ChangeStamp smalldatetime,
@ClassID varchar(50),
@CourseID int,
@Description varchar(255),
@Status int,
@Title varchar(10),
@Type int
AS
declare @new_id int
INSERT INTO dbo.[CourseSection] (
[Capacity],
[ChangeStamp],
[ClassID],
[CourseID],
[Description],
[Status],
[Title],
[Type]
) VALUES (
@Capacity,
@ChangeStamp,
@ClassID,
@CourseID,
@Description,
@Status,
@Title,
@Type
)
set @new_id = SCOPE_IDENTITY()
SELECT
[ID],
[Title],
[Description],
[Capacity],
[CourseID],
[ClassID],
[Status],
[Type],
[ChangeStamp]
FROM dbo.[CourseSection]
WHERE
[ID] = @new_id
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the CourseSection table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseSection_Update
@ID int,
@Capacity int,
@ChangeStamp smalldatetime,
@ClassID varchar(50),
@CourseID int,
@Description varchar(255),
@Status int,
@Title varchar(10),
@Type int
AS
UPDATE dbo.[CourseSection]
SET
[Capacity] = @Capacity,
[ChangeStamp] = @ChangeStamp,
[ClassID] = @ClassID,
[CourseID] = @CourseID,
[Description] = @Description,
[Status] = @Status,
[Title] = @Title,
[Type] = @Type
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
SELECT
[ID],
[Title],
[Description],
[Capacity],
[CourseID],
[ClassID],
[Status],
[Type],
[ChangeStamp]
FROM dbo.[CourseSection]
WHERE
[ID] = @ID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the CourseSection table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseSection_Delete
@ID int
,
@ChangeStamp smalldatetime
AS
DELETE FROM dbo.[CourseSection]
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseSection table through a foreign key
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseSection_GetByCourseID
@CourseID int
AS
SELECT
[ID],
[Title],
[Description],
[Capacity],
[CourseID],
[ClassID],
[Status],
[Type],
[ChangeStamp]
FROM
dbo.[CourseSection]
WHERE
[CourseID] = @CourseID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseSection table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseSection_GetByID
@ID int
AS
SELECT
[ID],
[Title],
[Description],
[Capacity],
[CourseID],
[ClassID],
[Status],
[Type],
[ChangeStamp]
FROM
[dbo].[CourseSection]
WHERE
[ID] = @ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- Drop the dbo.prc_CourseTakenByStudent_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_Get_List
GO
-- Drop the dbo.prc_CourseTakenByStudent_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetPaged
GO
-- Drop the dbo.prc_CourseTakenByStudent_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_Insert
GO
-- Drop the dbo.prc_CourseTakenByStudent_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_Update
GO
-- Drop the dbo.prc_CourseTakenByStudent_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_Delete
GO
-- Drop the dbo.prc_CourseTakenByStudent_GetByCourseID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetByCourseID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetByCourseID
GO
-- Drop the dbo.prc_CourseTakenByStudent_GetBySectionID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetBySectionID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetBySectionID
GO
-- Drop the dbo.prc_CourseTakenByStudent_GetByStudentID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetByStudentID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetByStudentID
GO
-- Drop the dbo.prc_CourseTakenByStudent_GetBySectionIDStudentID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetBySectionIDStudentID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetBySectionIDStudentID
GO
-- Drop the dbo.prc_CourseTakenByStudent_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetByID
GO
-- Drop the dbo.prc_CourseTakenByStudent_GetByStudentIDCourseID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_CourseTakenByStudent_GetByStudentIDCourseID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_CourseTakenByStudent_GetByStudentIDCourseID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the CourseTakenByStudent table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_Get_List
AS
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM
dbo.[CourseTakenByStudent]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the CourseTakenByStudent table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetPaged
(
@WhereClause varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[CourseTakenByStudent]'
IF @WhereClause IS NOT NULL
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF @OrderBy IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return total count
SELECT @@ROWCOUNT
--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn
-- Return paged results
SELECT O.ID, O.StudentID, O.CourseID, O.SectionID, O.Status, O.NonCredit, O.ChangeStamp
FROM
dbo.[CourseTakenByStudent] O,
#PageIndex PageIndex
WHERE
O.ID = PageIndex.ID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the CourseTakenByStudent table and returns the new PK
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_Insert
@ChangeStamp smalldatetime,
@CourseID int,
@NonCredit bit,
@SectionID int,
@Status int,
@StudentID int
AS
declare @new_id int
INSERT INTO dbo.[CourseTakenByStudent] (
[ChangeStamp],
[CourseID],
[NonCredit],
[SectionID],
[Status],
[StudentID]
) VALUES (
@ChangeStamp,
@CourseID,
@NonCredit,
@SectionID,
@Status,
@StudentID
)
set @new_id = SCOPE_IDENTITY()
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM dbo.[CourseTakenByStudent]
WHERE
[ID] = @new_id
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the CourseTakenByStudent table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_Update
@ID int,
@ChangeStamp smalldatetime,
@CourseID int,
@NonCredit bit,
@SectionID int,
@Status int,
@StudentID int
AS
UPDATE dbo.[CourseTakenByStudent]
SET
[ChangeStamp] = @ChangeStamp,
[CourseID] = @CourseID,
[NonCredit] = @NonCredit,
[SectionID] = @SectionID,
[Status] = @Status,
[StudentID] = @StudentID
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM dbo.[CourseTakenByStudent]
WHERE
[ID] = @ID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the CourseTakenByStudent table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_Delete
@ID int
,
@ChangeStamp smalldatetime
AS
DELETE FROM dbo.[CourseTakenByStudent]
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through a foreign key
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetByCourseID
@CourseID int
AS
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM
dbo.[CourseTakenByStudent]
WHERE
[CourseID] = @CourseID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through a foreign key
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetBySectionID
@SectionID int
AS
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM
dbo.[CourseTakenByStudent]
WHERE
[SectionID] = @SectionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through a foreign key
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetByStudentID
@StudentID int
AS
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM
dbo.[CourseTakenByStudent]
WHERE
[StudentID] = @StudentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetBySectionIDStudentID
@SectionID int,
@StudentID int
AS
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM
[dbo].[CourseTakenByStudent]
WHERE
[SectionID] = @SectionID
AND [StudentID] = @StudentID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetByID
@ID int
AS
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM
[dbo].[CourseTakenByStudent]
WHERE
[ID] = @ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the CourseTakenByStudent table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_CourseTakenByStudent_GetByStudentIDCourseID
@StudentID int,
@CourseID int
AS
SELECT
[ID],
[StudentID],
[CourseID],
[SectionID],
[Status],
[NonCredit],
[ChangeStamp]
FROM
[dbo].[CourseTakenByStudent]
WHERE
[StudentID] = @StudentID
AND [CourseID] = @CourseID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- Drop the dbo.prc_Permission_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_Get_List
GO
-- Drop the dbo.prc_Permission_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_GetPaged
GO
-- Drop the dbo.prc_Permission_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_Insert
GO
-- Drop the dbo.prc_Permission_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_Update
GO
-- Drop the dbo.prc_Permission_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_Delete
GO
-- Drop the dbo.prc_Permission_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Permission_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Permission_GetByID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Permission table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Permission_Get_List
AS
SELECT
[ID],
[Title],
[Description],
[ChangeStamp]
FROM
dbo.[Permission]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Permission table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Permission_GetPaged
(
@WhereClause varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Permission]'
IF @WhereClause IS NOT NULL
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF @OrderBy IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return total count
SELECT @@ROWCOUNT
--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn
-- Return paged results
SELECT O.ID, O.Title, O.Description, O.ChangeStamp
FROM
dbo.[Permission] O,
#PageIndex PageIndex
WHERE
O.ID = PageIndex.ID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Permission table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Permission_Insert
@ChangeStamp smalldatetime,
@Description varchar(255),
@ID int,
@Title varchar(50)
AS
INSERT INTO dbo.[Permission](
[ChangeStamp],
[Description],
[ID],
[Title]
) VALUES (
@ChangeStamp,
@Description,
@ID,
@Title
)
SELECT
[ID],
[Title],
[Description],
[ChangeStamp]
FROM dbo.[Permission]
WHERE
([ID] = @ID)
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Permission table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Permission_Update
@ID int ,
@ChangeStamp smalldatetime,
@Description varchar(255),
@Title varchar(50),
@OriginalID int
AS
UPDATE dbo.[Permission]
SET
[ChangeStamp] = @ChangeStamp,
[Description] = @Description,
[Title] = @Title,
[ID] = @OriginalID
WHERE
[ID] = @OriginalID
AND ChangeStamp = @ChangeStamp
SELECT
[ID],
[Title],
[Description],
[ChangeStamp]
FROM dbo.[Permission]
WHERE
[ID] = @OriginalID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Permission table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Permission_Delete
@ID int
,
@ChangeStamp smalldatetime
AS
DELETE FROM dbo.[Permission]
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Permission table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Permission_GetByID
@ID int
AS
SELECT
[ID],
[Title],
[Description],
[ChangeStamp]
FROM
[dbo].[Permission]
WHERE
[ID] = @ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- Drop the dbo.prc_SectionRoutine_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_Get_List
GO
-- Drop the dbo.prc_SectionRoutine_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_GetPaged
GO
-- Drop the dbo.prc_SectionRoutine_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_Insert
GO
-- Drop the dbo.prc_SectionRoutine_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_Update
GO
-- Drop the dbo.prc_SectionRoutine_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_Delete
GO
-- Drop the dbo.prc_SectionRoutine_GetBySectionID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_GetBySectionID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_GetBySectionID
GO
-- Drop the dbo.prc_SectionRoutine_GetBySectionID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_GetBySectionID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_GetBySectionID
GO
-- Drop the dbo.prc_SectionRoutine_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_SectionRoutine_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_SectionRoutine_GetByID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the SectionRoutine table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_SectionRoutine_Get_List
AS
SELECT
[ID],
[SectionID],
[StartTime],
[EndTime],
[ClassType],
[ChangeStamp]
FROM
dbo.[SectionRoutine]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the SectionRoutine table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_SectionRoutine_GetPaged
(
@WhereClause varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[SectionRoutine]'
IF @WhereClause IS NOT NULL
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF @OrderBy IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return total count
SELECT @@ROWCOUNT
--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn
-- Return paged results
SELECT O.ID, O.SectionID, O.StartTime, O.EndTime, O.ClassType, O.ChangeStamp
FROM
dbo.[SectionRoutine] O,
#PageIndex PageIndex
WHERE
O.ID = PageIndex.ID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the SectionRoutine table and returns the new PK
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_SectionRoutine_Insert
@ChangeStamp smalldatetime,
@ClassType int,
@EndTime int,
@SectionID int,
@StartTime int
AS
declare @new_id int
INSERT INTO dbo.[SectionRoutine] (
[ChangeStamp],
[ClassType],
[EndTime],
[SectionID],
[StartTime]
) VALUES (
@ChangeStamp,
@ClassType,
@EndTime,
@SectionID,
@StartTime
)
set @new_id = SCOPE_IDENTITY()
SELECT
[ID],
[SectionID],
[StartTime],
[EndTime],
[ClassType],
[ChangeStamp]
FROM dbo.[SectionRoutine]
WHERE
[ID] = @new_id
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the SectionRoutine table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_SectionRoutine_Update
@ID int,
@ChangeStamp smalldatetime,
@ClassType int,
@EndTime int,
@SectionID int,
@StartTime int
AS
UPDATE dbo.[SectionRoutine]
SET
[ChangeStamp] = @ChangeStamp,
[ClassType] = @ClassType,
[EndTime] = @EndTime,
[SectionID] = @SectionID,
[StartTime] = @StartTime
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
SELECT
[ID],
[SectionID],
[StartTime],
[EndTime],
[ClassType],
[ChangeStamp]
FROM dbo.[SectionRoutine]
WHERE
[ID] = @ID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the SectionRoutine table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_SectionRoutine_Delete
@ID int
,
@ChangeStamp smalldatetime
AS
DELETE FROM dbo.[SectionRoutine]
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the SectionRoutine table through a foreign key
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_SectionRoutine_GetBySectionID
@SectionID int
AS
SELECT
[ID],
[SectionID],
[StartTime],
[EndTime],
[ClassType],
[ChangeStamp]
FROM
dbo.[SectionRoutine]
WHERE
[SectionID] = @SectionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the SectionRoutine table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_SectionRoutine_GetByID
@ID int
AS
SELECT
[ID],
[SectionID],
[StartTime],
[EndTime],
[ClassType],
[ChangeStamp]
FROM
[dbo].[SectionRoutine]
WHERE
[ID] = @ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- Drop the dbo.prc_Student_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_Get_List
GO
-- Drop the dbo.prc_Student_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_GetPaged
GO
-- Drop the dbo.prc_Student_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_Insert
GO
-- Drop the dbo.prc_Student_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_Update
GO
-- Drop the dbo.prc_Student_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_Delete
GO
-- Drop the dbo.prc_Student_GetByID procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.prc_Student_GetByID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.prc_Student_GetByID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets all records from the Student table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Student_Get_List
AS
SELECT
[ID],
[FirstName],
[MiddleName],
[LastName],
[PresentAddress1],
[PresentAddress2],
[PermanentAddress1],
[PermanentAddress2],
[StudentID],
[Status],
[CGPA],
[CreditCompleted],
[DateAdmitted],
[DateGraduated],
[ChangeStamp]
FROM
dbo.[Student]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Gets records from the Student table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Student_GetPaged
(
@WhereClause varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
[IndexId] int IDENTITY (1, 1) NOT NULL,
[ID] int
)
-- Insert into the temp table
declare @SQL as nvarchar(3500)
SET @SQL = 'INSERT INTO #PageIndex (ID)'
SET @SQL = @SQL + ' SELECT [ID]'
SET @SQL = @SQL + ' FROM dbo.[Student]'
IF @WhereClause IS NOT NULL
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
END
IF @OrderBy IS NOT NULL
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
END
-- Populate the temp table
exec sp_executesql @SQL
-- Return total count
SELECT @@ROWCOUNT
--Set RowCount After Total Rows is determined
SET ROWCOUNT @RowsToReturn
-- Return paged results
SELECT O.ID, O.FirstName, O.MiddleName, O.LastName, O.PresentAddress1, O.PresentAddress2, O.PermanentAddress1, O.PermanentAddress2, O.StudentID, O.Status, O.CGPA, O.CreditCompleted, O.DateAdmitted, O.DateGraduated, O.ChangeStamp
FROM
dbo.[Student] O,
#PageIndex PageIndex
WHERE
O.ID = PageIndex.ID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Inserts a record into the Student table and returns the new PK
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Student_Insert
@CGPA real,
@ChangeStamp smalldatetime,
@CreditCompleted int,
@DateAdmitted datetime,
@DateGraduated datetime,
@FirstName varchar(50),
@LastName varchar(50),
@MiddleName varchar(50),
@PermanentAddress1 varchar(255),
@PermanentAddress2 varchar(255),
@PresentAddress1 varchar(255),
@PresentAddress2 varchar(255),
@Status int,
@StudentID varchar(50)
AS
declare @new_id int
INSERT INTO dbo.[Student] (
[CGPA],
[ChangeStamp],
[CreditCompleted],
[DateAdmitted],
[DateGraduated],
[FirstName],
[LastName],
[MiddleName],
[PermanentAddress1],
[PermanentAddress2],
[PresentAddress1],
[PresentAddress2],
[Status],
[StudentID]
) VALUES (
@CGPA,
@ChangeStamp,
@CreditCompleted,
@DateAdmitted,
@DateGraduated,
@FirstName,
@LastName,
@MiddleName,
@PermanentAddress1,
@PermanentAddress2,
@PresentAddress1,
@PresentAddress2,
@Status,
@StudentID
)
set @new_id = SCOPE_IDENTITY()
SELECT
[ID],
[FirstName],
[MiddleName],
[LastName],
[PresentAddress1],
[PresentAddress2],
[PermanentAddress1],
[PermanentAddress2],
[StudentID],
[Status],
[CGPA],
[CreditCompleted],
[DateAdmitted],
[DateGraduated],
[ChangeStamp]
FROM dbo.[Student]
WHERE
[ID] = @new_id
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Updates a record in the Student table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Student_Update
@ID int,
@CGPA real,
@ChangeStamp smalldatetime,
@CreditCompleted int,
@DateAdmitted datetime,
@DateGraduated datetime,
@FirstName varchar(50),
@LastName varchar(50),
@MiddleName varchar(50),
@PermanentAddress1 varchar(255),
@PermanentAddress2 varchar(255),
@PresentAddress1 varchar(255),
@PresentAddress2 varchar(255),
@Status int,
@StudentID varchar(50)
AS
UPDATE dbo.[Student]
SET
[CGPA] = @CGPA,
[ChangeStamp] = @ChangeStamp,
[CreditCompleted] = @CreditCompleted,
[DateAdmitted] = @DateAdmitted,
[DateGraduated] = @DateGraduated,
[FirstName] = @FirstName,
[LastName] = @LastName,
[MiddleName] = @MiddleName,
[PermanentAddress1] = @PermanentAddress1,
[PermanentAddress2] = @PermanentAddress2,
[PresentAddress1] = @PresentAddress1,
[PresentAddress2] = @PresentAddress2,
[Status] = @Status,
[StudentID] = @StudentID
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
SELECT
[ID],
[FirstName],
[MiddleName],
[LastName],
[PresentAddress1],
[PresentAddress2],
[PermanentAddress1],
[PermanentAddress2],
[StudentID],
[Status],
[CGPA],
[CreditCompleted],
[DateAdmitted],
[DateGraduated],
[ChangeStamp]
FROM dbo.[Student]
WHERE
[ID] = @ID
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Deletes a record in the Student table
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Student_Delete
@ID int
,
@ChangeStamp smalldatetime
AS
DELETE FROM dbo.[Student]
WHERE
[ID] = @ID
AND [ChangeStamp] = @ChangeStamp
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------------------------------------
-- Purpose: Select records from the Student table through an index
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.prc_Student_GetByID
@ID int
AS
SELECT
[ID],
[FirstName],
[MiddleName],
[LastName],
[PresentAddress1],
[PresentAddress2],
[PermanentAddress1],
[PermanentAddress2],
[StudentID],
[Status],
[CGPA],
[CreditCompleted],
[DateAdmitted],
[DateGraduated],
[ChangeStamp]
FROM
[dbo].[Student]
WHERE
[ID] = @ID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Submit an article or tip