Friday, 28 March 2014

Get page wise result data in SQL Server



CREATE PROC usp_GetPagingData
      @CurrentPage INT = NULL,
      @PageSize INT = NULL
AS
BEGIN
      DECLARE @RowsFrom INT
      DECLARE @RowsTo INT
      DECLARE @TotalRecords INT
      DECLARE @NoOfRecs INT
      DECLARE @RecordSize NVARCHAR(MAX)
      DECLARE @TotalPage INT
     
     
      -- Get result data
      SELECT ID, Code, Name_en
      INTO #Result
      FROM TblAreas
     
     

      SET @RowsFrom = @CurrentPage * @PageSize - @PageSize + 1
      SET @RowsTo = @CurrentPage * @PageSize
     

      SELECT @TotalRecords = count(1)
      FROM #Result

      SELECT @NoOfRecs = count(1)
      FROM #Result

      IF (@PageSize != NULL) OR (@PageSize > 0)
      BEGIN
            SET @RowsFrom = @CurrentPage * @PageSize - @PageSize + 1
            SET @RowsTo = @CurrentPage * @PageSize
           
            print @PageSize
            print @RowsFrom
            print @RowsTo          
      END              
      ELSE
      BEGIN
            SET @PageSize = @NoOfRecs          
            SET @RowsFrom = 1
            SET @RowsTo = @NoOfRecs      
           
            print @PageSize
            print @RowsFrom
            print @RowsTo
      END

      SET @RecordSize = CONVERT(VARCHAR, @PageSize)

      IF (@TotalRecords % @RecordSize <> 0)
      BEGIN
            SELECT @TotalPage = CEILING(@TotalRecords / @RecordSize) + 1
      END
      ELSE
      BEGIN
            SELECT @TotalPage = CEILING(@TotalRecords / @RecordSize)
      END

      SELECT @TotalPage TotalPage

      SELECT @TotalRecords TotalRecords;

      WITH Result
      AS (
            SELECT *,
                  ROW_NUMBER() OVER (
                        ORDER BY ID
                        ) AS RowNumber
            FROM #Result
            )
           
      SELECT *
      FROM Result
      WHERE RowNumber >= CONVERT(NVARCHAR(10), isnull(@RowsFrom, 0))
            AND RowNumber <= CONVERT(NVARCHAR(10), isnull(@RowsTo, @NoOfRecs))

      DROP TABLE #Result
END

Go

EXEC usp_GetPagingData 2, 5

Output:


Thursday, 27 March 2014

How to select node values from XML in SQL Server

CREATE FUNCTION Fn_GetSelectedFilters (  
      @XmlData NVARCHAR(MAX)
)
RETURNS @FilterData TABLE (
      Filter INT,
      FilterType NVARCHAR(200)
      )
AS
BEGIN
      DECLARE @XML AS XML;

      SET @XML = CAST(@XmlData AS XML)
                 
      ------------------- Start : Regions -----------------
      INSERT INTO @FilterData
      SELECT xmlData.Col.value('.', 'varchar(max)'),
            'Regions'
      FROM @XML.nodes('//Regions/int') xmlData(Col);       
      ------------------- End : Regions -------------------

     
      ------------------- Start : Countries ---------------
      INSERT INTO @FilterData
      SELECT xmlData.Col.value('.', 'varchar(max)'),
            'Countries'
      FROM @XML.nodes('//Countries/int') xmlData(Col);     
      ------------------- End : Countries -----------------            
     
RETURN
END


GO

SELECT * FROM dbo.Fn_GetSelectedFilters('<SelectedFilters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Regions>
    <int>1</int>
    <int>2</int>
  </Regions>
  <Countries>
    <int>101</int>
    <int>102</int>
    <int>301</int>
  </Countries> 
</SelectedFilters>')


Output:

Filter      FilterType
----------- -------------------
1           Regions
2           Regions
101         Countries
102         Countries
301         Countries

(5 row(s) affected)