T-SQL Row_Number() and Paging

In MS SQL 2005 you can use Row_Number() function for paging:

DECLARE @PageNumber AS INT;
DECLARE @PageSize AS INT;
SET @PageNumber = 2;
SET @PageSize = 10;

WITH CustomerCTE AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY LoginDate, CustomerID) AS RowNumber
          ,CustomerID
          ,LoginDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Customers
)

SELECT *
  FROM CustomerCTE
 WHERE RowNumber BETWEEN (@PageNumber – 1) * @PageSize + 1 AND @PageNumber * @PageSize
 ORDER BY LoginDate , CustomerID;

Advertisements

Together one column’s data in one string

There is a useful expression in T-SQL  “COALESCE()” we can use it for combining a column data into a string easyly

For example

Declare @sumtextnvarchar(100)
select top 5 @sumtext = Coalesce(@sumtext + ‘;’ ,) + Cast(Name as nvarchar)
from Country
print @sumtext

Output:

Afrika;Australi en Nieuw-Zeeland;Belgi;Benelux;Bulgarije;Canada;China;Duitsland;EU-landen;Frankrij

May the code be with you !