Stored Procedure

USE [AspnetB11]
GO
/****** Object:  StoredProcedure [dbo].[GetCourseEnrollments]    Script Date: 12/27/2024 11:17:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[GetCourseEnrollments]
@PageIndex INT,
@PageSize INT,
@OrderBy nvarchar(50),
@CourseName nvarchar(250) = '%',
@StudentName nvarchar(250) = '%',
@FeeFrom DECIMAL = NULL,
@FeeTo DECIMAL = NULL,
@Total INT output,
@TotalDisplay INT output

AS
BEGIN

    DECLARE @SQL nvarchar(2000);
    DECLARE @countSql nvarchar(2000);
    DECLARE @paramList nvarchar(MAX);
    DECLARE @countparamList nvarchar(MAX);
    DECLARE @xTotalDisplay INT;

    SELECT @Total = COUNT(*) FROM CourseStudents;

    SET @countsql = 'select @xTotalDisplay = count(*) from CourseStudents cs inner join
                    Courses c on cs.CourseId = c.Id inner join
                    Students s on cs.StudentId = s.Id  where 1 = 1 ';

                    IF @CourseName IS NOT NULL
                    SET @countsql = @countsql + ' AND c.Name LIKE ''%'' + @xCourseName + ''%'''

                    IF @StudentName IS NOT NULL
                    SET @countsql = @countsql + ' AND s.Name LIKE ''%'' + @xStudentName + ''%'''

                    IF @FeeFrom IS NOT NULL
                    SET @countsql = @countsql + ' AND Fee >= @xFeeFrom'

                    IF @FeeTo IS NOT NULL
                    SET @countsql = @countsql + ' AND Fee <= @xFeeTo'

    SET @SQL = 'select c.Name as CourseName, s.Name as StudentName, c.Fee from CourseStudents cs inner join
                Courses c on cs.CourseId = c.Id inner join
                Students s on cs.StudentId = s.Id where 1 = 1 ';

                IF @CourseName IS NOT NULL
                SET @SQL = @SQL + ' AND c.Name LIKE ''%'' + @xCourseName + ''%'''

                IF @StudentName IS NOT NULL
                SET @SQL = @SQL + ' AND s.Name LIKE ''%'' + @xStudentName + ''%'''

                IF @FeeFrom IS NOT NULL
                SET @SQL = @SQL + ' AND Fee >= @xFeeFrom'

                IF @FeeTo IS NOT NULL
                SET @SQL = @SQL + ' AND Fee <= @xFeeTo'



                SET @SQL = @SQL + ' Order by '+@OrderBy+' OFFSET @xPageSize * (@xPageIndex - 1)
                ROWS FETCH NEXT @xPageSize ROWS ONLY';


    SELECT @countparamlist = '@xCourseName nvarchar(250),
        @xStudentName nvarchar(250),
        @xFeeFrom decimal,
        @xFeeTo decimal,
        @xTotalDisplay int output' ;

    EXEC sp_executesql @countsql, @countparamlist,
                        @CourseName,
                        @StudentName,
                        @FeeFrom,
                        @FeeTo,
                        @xTotalDisplay = @TotalDisplay output;


    SELECT @paramlist = '@xCourseName nvarchar(250),
        @xStudentName nvarchar(250),
        @xFeeFrom decimal,
        @xFeeTo decimal,
        @xPageIndex int,
        @xPageSize int';

    EXEC sp_executesql @SQL , @paramlist ,
        @CourseName,
        @StudentName,
        @FeeFrom,
        @FeeTo,
        @PageIndex,
        @PageSize;

    print @countsql;
    print @SQL;
END