Using SQL Server
Stored Procedure for implementing Custom Paging:
All of us would have
implemented Paging in our applications.
Paging is particularly useful if you have lots of records to be
displayed on a page and you can't get them displayed in one stretch. Say we
have 1000 records to be displayed in a page. In this scenario, we cannot show
up all the records in a single stretch in the page. Hence we need to implement
Paging functionality whereby users can see a set of records and then click on a
Button/Link to view the next set of records.
Here is
the sample store procedure that is used for the SQL Server store procedure for
paging.
Example:
CREATEPROCEDURE USP_PRODUCT_SELECT ( @CurrentIndex int, @TotalPageSize int ) AS
SELECT*FROM (
SELECTROW_NUMBER()OVER(ORDER BY ProductID)AS RowId, * FROM Products
)AS P WHERE P.RowId
BETWEEN (@CurrentIndex
-1)* @TotalPageSize+ 1 AND (@CurrentIndex
* @TotalPageSize )
A
common table expression (CTE) provides the significant advantage of being
able to reference itself, thereby creating a recursive CTE. A recursive CTE
is one in which an initial CTE is repeatedly executed to return subsets of
data until the complete result set is obtained.
A query is referred to as a recursive query
when it references a recursive CTE. Returning hierarchical data is a common use
of recursive queries, for example: Displaying employees in an organizational
chart, or data in a bill of materials scenario in which a parent product has
one or more components and those components may, in turn, have subcomponents or
may be components of other parents.
A recursive CTE can greatly simplify the code
required to run a recursive query within a SELECT, INSERT, UPDATE,
DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, a
recursive query usually requires using temporary tables, cursors, and logic to
control the flow of the recursive steps.
The structure of the recursive Query
The structure of the recursive CTE in
Transact-SQL is similar to recursive routines in other programming
languages. Although a recursive routine in other languages returns a scalar
value, a recursive CTE can return multiple rows.
A recursive CTE consists of three elements:
Invocation of the
routine. The first invocation of the recursive CTE
consists of one or more CTE_query_definitions joined by UNION
ALL, UNION, EXCEPT, or INTERSECT
operators. Because these query definitions form the base result set of the
CTE structure, they are referred to as anchor members. CTE_query_definitions are considered anchor members unless they
reference the CTE itself. All anchor-member query definitions must
be positioned before the first recursive member definition, and a UNION
ALL operator must be used to join the last anchor member with the
first recursive member.
Recursive invocation
of the routine. The recursive invocation includes one or
more CTE_query_definitions joined by UNION ALL operators
that reference the CTE itself. These query definitions are referred
to as recursive members.
Termination check. The termination check is implicit;
recursion stops when no rows are returned from the previous invocation.
Recursive query in sql
server Example:
WITH CTE(categoryId,CategoryName,ParentCategoryID,TopParentCategoryID)AS
(
SELECT
a.CategoryId,
a.CategoryName,
a.ParentCategoryID,
a.CategoryId AS
TopParentCategoryID
FROM Categories a WHERE ParentCategoryID isnull
UNION
ALL
SELECT c.CategoryId, c.CategoryName, c.ParentCategoryID, cte.TopParentCategoryID
AS TopParentCategoryID FROM
Categories c,cte
WHERE cte.CategoryId
= c.ParentCategoryID
)
SELECT CTE.categoryId,cte.CategoryName,cte.ParentCategoryID,cte.TopParentCategoryID
FROM CTE
Welcome to Rajesh Prajapati, asp.net blog. Here you can find some useful code and information about asp.net., c#, VB.net, SQL Server, Web Service, Web Designing etc
