--==============================
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
No comments:
Post a Comment