Create Procedure for Pagination SQL Query

--==============================

Create Table Query

CREATE TABLE [dbo].[Student]

(

    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    Name varchar(50) NOT NULL,

    Address varchar(100) NULL,

    Age int NOT NULL,

    Standard varchar(10) NOT NULL,

    [Percent] decimal(5, 2) NOT NULL,

    AddedOn datetime NOT NULL DEFAULT GETDATE(),

    Status bit NOT NULL

)

--==============================

Create Procedure for Insert Data

CREATE PROCEDURE [dbo].[sp_InsertStudent]   

    @Name       VARCHAR(50), 

    @Address        VARCHAR(100),   

    @Age        INT,

    @Standard     VARCHAR(10),

    @Percent        DECIMAL(5,2),

    @Status       BIT, 

    @Result     VARCHAR(50) OUTPUT,

    @CreatedId    INT OUTPUT     

AS           

BEGIN

    INSERT INTO Student(Name,Address,Age,Standard,[Percent],Status) VALUES (@Name,@Address,@Age,@Standard,@Percent,@Status) 

    SET @Result='Insert Successful'

    SET @CreatedId=@@IDENTITY

END

--===============================

CReate Procedure to get Student by Id

CREATE PROCEDURE [dbo].[sp_GetStudentById]  

    @Id           INT

AS           

BEGIN

    SELECT * FROM Student WHERE id=@Id

END

---=======================

CReate Procedure for Pagination wise data for Performance

CREATE PROCEDURE [dbo].[sp_GetStudent] 

    @PageNo       INT,

    @PageSize       INT

AS           

BEGIN

    DECLARE @qry  NVARCHAR(1000);   

    SET @qry='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY AddedON DESC) AS ''RowNum'',* FROM Student WHERE 1=1) a WHERE a.RowNum BETWEEN ('+CAST(@pageNo AS NVARCHAR(5))+'-1)*'+CAST(@pageSize AS NVARCHAR(5))+'+1 AND ('+CAST(@pageNo AS NVARCHAR(5))+'*'+CAST(@pageSize AS NVARCHAR(5))+')'

    EXEC Sp_executesql @qry

END