sqlserver pagingsearching and sorting

Question

Hi All i have small doubt about a stored writing as a combination of pagingsorting and searching. For example i have 2 tables like users-user idusernamepasswordregion id regions-regionidregion name I am trying the stored procedure for searching and sorting.But i did not understand the paging. The stored proc having parameters like colnamesearchnamepageindexpagesize. Can any one help to solve this one?

Aarti · Answer

Hi Satish use below code ---------- declare @pageindex int@PageSize int@OrderBy varchar(max)@Searchtxt varchar(MAX) ----------------------- declare @strtdata int declare @enddata int ------------------------------paging logic--------------------------------- if (@pageIndex = 0) begin set @strtdata = 0 set @enddata = @pageSize end else begin set @strtdata = (((@pageIndex - 1) + 1) * @pageSize) + 1 set @enddata = (((@pageIndex - 1) + 1) * @pageSize) + @pageSize end ------------------------------------------------------------------------------------------ set @Searchtxt='where columnname like ''%'+Convert(varchar@Searchtxt) +'%''' declare @qry varchar(max)='' set @qry=' select * ROW_NUMBER() OVER (ORDER BY ColumnName desc ) rownum from Table 1 where '+@Searchtxt+' ' exec(@qry)

Jignesh Trivedi · Answer

hi Agree with Aarti using ROW_NUMBER() function you can easily done paging. here you must create dynamic query.... Please refer http://www.codeguru.com/csharp/.net/net_data/article.php/c19611/Paging-in-SQL-Server-2005.htm http://www.c-sharpcorner.com/UploadFile/6bb76f/searching-sorting-and-paging-using-sql-query/ hope this will help you.

Akkiraju Ivaturi · Answer

Check if the following URL works: http://consultingblogs.emc.com/rorystreet/archive/2008/01/29/Paging-filtering_2C00_searching-and-sorting-in-one-stored-procedure.aspx

sqlserver paging,searching and sorting