Youtor
Offset fetch next in SQL Server 2012
>> YOUR LINK HERE: ___ http://youtube.com/watch?v=7JINCqgT_cQ
sql server 2012 offset fetch next • sql server 2012 paging stored procedure • sql server offset fetch example • mssql offset fetch • In this video we will discuss OFFSET FETCH Clause in SQL Server 2012 • Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. • / @aarvikitchen5572 • One of the common tasks for a SQL developer is to come up with a stored procedure that can return a page of results from the result set. With SQL Server 2012 OFFSET FETCH Clause it is very easy to implement paging. • Let's understand this with an example. We will use the following tblProducts table for the examples in this video. • SQL Script to create tblProducts table • Create table tblProducts • ( • Id int primary key identity, • Name nvarchar(25), • [Description] nvarchar(50), • Price int • ) • Go • SQL Script to populate tblProducts table with 100 rows • Declare @Start int • Set @Start = 1 • Declare @Name varchar(25) • Declare @Description varchar(50) • While(@Start [= 100) • Begin • Set @Name = 'Product - ' + LTRIM(@Start) • Set @Description = 'Product Description - ' + LTRIM(@Start) • Insert into tblProducts values (@Name, @Description, @Start * 10) • Set @Start = @Start + 1 • End • OFFSET FETCH Clause • Introduced in SQL Server 2012 • Returns a page of results from the result set • ORDER BY clause is required • OFFSET FETCH Syntax : • SELECT * FROM Table_Name • ORDER BY Column_List • OFFSET Rows_To_Skip ROWS • FETCH NEXT Rows_To_Fetch ROWS ONLY • The following SQL query • 1. Sorts the table data by Id column • 2. Skips the first 10 rows and • 3. Fetches the next 10 rows • SELECT * FROM tblProducts • ORDER BY Id • OFFSET 10 ROWS • FETCH NEXT 10 ROWS ONLY • From the front-end application, we would typically send the PAGE NUMBER and the PAGE SIZE to get a page of rows. The following stored procedure accepts PAGE NUMBER and the PAGE SIZE as parameters and returns the correct set of rows. • CREATE PROCEDURE spGetRowsByPageNumberAndSize • @PageNumber INT, • @PageSize INT • AS • BEGIN • SELECT * FROM tblProducts • ORDER BY Id • OFFSET (@PageNumber - 1) * @PageSize ROWS • FETCH NEXT @PageSize ROWS ONLY • END • With PageNumber = 3 and PageSize = 10, the stored procedure returns the correct set of rows • EXECUTE spGetRowsByPageNumberAndSize 3, 10 • Text version of the video • http://csharp-video-tutorials.blogspo... • Slides • http://csharp-video-tutorials.blogspo... • All SQL Server Text Articles • http://csharp-video-tutorials.blogspo... • All SQL Server Slides • http://csharp-video-tutorials.blogspo... • All Dot Net and SQL Server Tutorials in English • https://www.youtube.com/user/kudvenka... • All Dot Net and SQL Server Tutorials in Arabic • / kudvenkatarabic
#############################
