none
Using of paging in union, but union have also some conditions to meet.

    Question

  • Hi to all, How can use the paging (row_number) in union? The union of my queries are depends on the selected types. And this types are came from different databases. Example if type 1, i will query to database1. If type 2 i will query in database2, .....until database 4. If the parameter is type1and 2. It will query to database1and 2 only.

    3 stars and a sun

    Sunday, April 28, 2013 10:45 AM

Answers

  • Try the below code.

    ----------- User define function

    CREATE FUNCTION [dbo].[getWord](@sentence as NVARCHAR(500))
    RETURNS @temp table(word NVARCHAR(50))
    AS
    BEGIN
    	DECLARE @TmpWords NVARCHAR(500),@NewWord NVARCHAR(500)
    	DECLARE @splitBY nvarchar(2)
    	
    	SET @splitBY= ','
    	SET @TmpWords = replace(@sentence,'#','') + @splitBY
    	WHILE CHARINDEX(@splitBY, @TmpWords) > 0
    	BEGIN
    		SET @NewWord = LEFT(@TmpWords, CHARINDEX(@splitBY, @TmpWords))
    		SET @TmpWords = SUBSTRING(@TmpWords,CHARINDEX(@splitBY, @TmpWords)+1,len(@TmpWOrds))
    		insert into @temp values (rtrim(ltrim(replace(@NewWord,@splitBY,''))))
        END
    	RETURN 
    End

    -------------------------------  SQL (You can write store procedure)

    DECLARE @Type nvarchar(10),@ID NVARCHAR(10),@SOURCENAME NVARCHAR(20),@STRS NVARCHAR(MAX)
    SET @Type = '1,3'
    SET @STRS = ''
    DECLARE @TABLE TABLE (ID NVARCHAR(10), SOURCENAME NVARCHAR(20))
    INSERT INTO @TABLE VALUES (1,'Database1.dbo.Tbl1')
    INSERT INTO @TABLE VALUES (2,'Database2.dbo.Tbl1')
    INSERT INTO @TABLE VALUES (3,'Database3.dbo.Tbl1')
    INSERT INTO @TABLE VALUES (4,'Database4.dbo.Tbl1')
    DECLARE C CURSOR FOR SELECT ID,SOURCENAME FROM @TABLE a INNER JOIN (SELECT * FROM dbo.getWord(@Type)) b ON a.ID=b.word
    OPEN C
    FETCH NEXT FROM C INTO @ID,@SOURCENAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	-- APPLY PAGING IN THE BELOW STATEMENT.
    	SET @STRS = @STRS + 'SELECT * FROM ' + @SOURCENAME + ' UNION ALL '
    	FETCH NEXT FROM C INTO @ID,@SOURCENAME
    END
    CLOSE C
    DEALLOCATE C
    SET @STRS = (LEFT(@STRS, LEN(@STRS) - 11))
    PRINT @STRS
    --EXEC(@STRS)

    You can add Row_Number() to do paging.


    Regards, RSingh



    Sunday, April 28, 2013 12:32 PM

All replies

  • Can you post the query ?

    Regards, RSingh

    Sunday, April 28, 2013 10:59 AM
  • Hi For example. If type = 1 Begin Select * from database1.dbo.tbl1 End If type = 2 Begin Select * from database2.dbo.tbl1 End If the parameter is set to type 1,2 it will select all the queries and we can do some paging. What i'm thinking is to use temp table. But i dont want to use temp table.

    3 stars and a sun

    Sunday, April 28, 2013 11:08 AM
  • Are the both databases have same table structure? I think having IF..ELSE block in the procedure is the best choice.

    CREATE VIEW v1

    AS

    SELECT '1' AS Type , col1,col2 FROM db1.dbo.tbl1

    UNION ALL

    SELECT '2' AS Type , col1,col2 FROM db2.dbo.tbl1

    UNION ALL

    SELECT '1,2' AS Type , col1,col2 FROM db1.dbo.tbl1

    UNION ALL

    SELECT '1,2' AS Type , col1,col2 FROM db2.dbo.tbl1

    ---------------------------------

    See examples of paging...

    DECLARE @page_size INT;
    DECLARe @page_nbr INT;

    SET @page_size = 50;
    SET @page_nbr = 2;

    SELECT a, b, c, d
    FROM (SELECT a, b, c, d,
                        ROW_NUMBER() OVER(
                        ORDER BY a DESC, b DESC, 
                                      c DESC, d DESC)
          FROM SomeTable) AS T(a, b, c, d, rn)
    WHERE rn > (@page_nbr - 1) * @page_size 
       AND rn <= @page_nbr * @page_size;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, April 28, 2013 11:42 AM
    Answerer
  • Something like this,

    Declare @Type nvarchar(10)
    Set @Type = '1,2'
    With CTE (*,[DB])
    AS (
    	Select *,1 as [DB] from Database1.dbo.tbl1 UNION ALL
    	Select *,2 as [DB] from Database2.dbo.tbl1 UNION ALL
    	Select *,3 as [DB] from Database3.dbo.tbl1 UNION ALL
    	Select *,4 as [DB] from Database4.dbo.tbl1
    ) 
    Select * from CTE where [DB] in (@Type)


    Regards, RSingh


    Sunday, April 28, 2013 11:44 AM
  • No they are not the same structure Each results contains thousands of records. And if i query to all databases it makes so slow. if the user is only need is type 1,it will only query to database1. If the user need it all it will query to all databases

    3 stars and a sun

    Sunday, April 28, 2013 11:52 AM
  • Try the below code.

    ----------- User define function

    CREATE FUNCTION [dbo].[getWord](@sentence as NVARCHAR(500))
    RETURNS @temp table(word NVARCHAR(50))
    AS
    BEGIN
    	DECLARE @TmpWords NVARCHAR(500),@NewWord NVARCHAR(500)
    	DECLARE @splitBY nvarchar(2)
    	
    	SET @splitBY= ','
    	SET @TmpWords = replace(@sentence,'#','') + @splitBY
    	WHILE CHARINDEX(@splitBY, @TmpWords) > 0
    	BEGIN
    		SET @NewWord = LEFT(@TmpWords, CHARINDEX(@splitBY, @TmpWords))
    		SET @TmpWords = SUBSTRING(@TmpWords,CHARINDEX(@splitBY, @TmpWords)+1,len(@TmpWOrds))
    		insert into @temp values (rtrim(ltrim(replace(@NewWord,@splitBY,''))))
        END
    	RETURN 
    End

    -------------------------------  SQL (You can write store procedure)

    DECLARE @Type nvarchar(10),@ID NVARCHAR(10),@SOURCENAME NVARCHAR(20),@STRS NVARCHAR(MAX)
    SET @Type = '1,3'
    SET @STRS = ''
    DECLARE @TABLE TABLE (ID NVARCHAR(10), SOURCENAME NVARCHAR(20))
    INSERT INTO @TABLE VALUES (1,'Database1.dbo.Tbl1')
    INSERT INTO @TABLE VALUES (2,'Database2.dbo.Tbl1')
    INSERT INTO @TABLE VALUES (3,'Database3.dbo.Tbl1')
    INSERT INTO @TABLE VALUES (4,'Database4.dbo.Tbl1')
    DECLARE C CURSOR FOR SELECT ID,SOURCENAME FROM @TABLE a INNER JOIN (SELECT * FROM dbo.getWord(@Type)) b ON a.ID=b.word
    OPEN C
    FETCH NEXT FROM C INTO @ID,@SOURCENAME
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	-- APPLY PAGING IN THE BELOW STATEMENT.
    	SET @STRS = @STRS + 'SELECT * FROM ' + @SOURCENAME + ' UNION ALL '
    	FETCH NEXT FROM C INTO @ID,@SOURCENAME
    END
    CLOSE C
    DEALLOCATE C
    SET @STRS = (LEFT(@STRS, LEN(@STRS) - 11))
    PRINT @STRS
    --EXEC(@STRS)

    You can add Row_Number() to do paging.


    Regards, RSingh



    Sunday, April 28, 2013 12:32 PM
  • Hi,

    Use this:


     DECLARE          
         -- UI Paging criteria         
         @l_n_StartRow INT,          
         @l_n_EndRow INT,          
         @l_n_TotalRows INT,         
         @l_n_page INT = 1,         
         @l_n_recsPerPage INT = 20         
    declare  @l_c_OrderCol VARCHAR(8000)= NULL,         
         @l_c_OrderType VARCHAR(4) = 'ASC'         
    ;With Cte_tmp
    AS (
     Select * from mytable1 UNION ALL
     Select * from mytable2
    ) ,cte_tmp1 as(

    Select ROW_NUMBER() OVER
         ( 
          ORDER BY          
            CASE         
             WHEN @l_c_OrderCol = 'ColName' AND @l_c_OrderType='ASC'         
             THEN ENT.EntityId         
            END ASC,         
            CASE         
             WHEN @l_c_OrderCol = 'ColName' AND @l_c_OrderType='DESC'         
             THEN ENT.EntityId  END DESC  ) AS ROWNUM, 
         COUNT(*) OVER () AS TotalRows  , *     
            Cte_tmp )
            select * from cte_tmp1
            WHERE          
                 cte_tmp1.ROWNUM > =      
                 (     
         CASE          
          WHEN @l_n_recsPerPage ! = 0 AND @l_n_page ! = 0          
                   THEN @l_n_StartRow          
          ELSE 1          
         END          
        )      
        AND cte_tmp1.ROWNUM < =      
        (     
         CASE          
          WHEN @l_n_recsPerPage ! = 0 AND @l_n_page ! = 0          
          THEN @l_n_EndRow         
          ELSE cte_tmp1.TotalRows         
         END          
        )      
     ORDER BY cte_tmp1.ROWNUM ASC


    blue world

    Monday, April 29, 2013 12:12 PM