Click here to Skip to main content
12,450,937 members (37,280 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL SQL-Server
Is this the correct way to use UNION ALL in Stored Procedures ?

ALTER PROCEDURE [GetHomePageObjectPageWise]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@PageCount INT OUTPUT
      ,@whereStoryID varchar(2000)
      ,@whereAlbumID varchar(2000)
      ,@wherePictureID varchar(2000)
AS
BEGIN
      SET NOCOUNT ON;
 

 
      SELECT StoryID
      , AlbumID
      , StoryTitle
      , NULL AS AlbumName
      , (SELECT URL FROM AlbumPictures WHERE (AlbumID = dbo.Stories.AlbumID) AND (AlbumCover = 'True')) AS AlbumCover
      , Votes
      , NULL AS PictureId
      , 'stories' AS tableName
      , NEWID() AS Sort 
 
INTO #Results1
FROM Stories WHERE StoryID IN (SELECT StringVal FROM funcListToTableInt(@whereStoryID))
 

 

 

      SELECT    NULL AS StoryID
      , AlbumID
      , NULL AS StoryTitle
      , AlbumName
      , (SELECT URL FROM AlbumPictures AS AlbumPictures_3 WHERE (AlbumID = Albums.AlbumID) AND (AlbumCover = 'True')) AS AlbumCover
      , Votes
      , NULL AS PictureId
      , 'albums' AS tableName
      , NEWID() AS Sort
INTO #Results2
FROM Albums WHERE AlbumID IN (SELECT StringVal FROM funcListToTableInt(@whereAlbumID))
 

 

 

        SELECT NULL AS StoryID
        , NULL AS AlbumID
        , NULL AS StoryTitle
        , NULL AS AlbumName
        , URL
        , Votes
        , PictureID
        , 'pictures' AS tableName
        , NEWID() AS Sort
        INTO #Results3
FROM AlbumPictures AS AlbumPictures_1
WHERE PictureID IN (SELECT StringVal FROM funcListToTableInt(@wherePictureID))
 
SELECT * INTO #Results4 FROM #Results1
UNION ALL
SELECT * FROM #Results2
UNION ALL
SELECT * FROM #Results3
 
SELECT ROW_NUMBER() OVER
            (
                  ORDER BY [Sort] DESC
            )AS RowNumber
            , * INTO #Results
            FROM #Results4
 

      DECLARE @RecordCount INT
      SELECT @RecordCount = COUNT(*) FROM #Results
 
      SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
 

      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
 
      DROP TABLE #Results
      DROP TABLE #Results1
      DROP TABLE #Results2
      DROP TABLE #Results3
      DROP TABLE #Results4
END


ALTER PROCEDURE [GetHomePageObjectPageWise]
      @PageIndex        INT = 1
      ,@PageSize        INT = 10
      ,@PageCount       INT OUTPUT
      ,@whereStoryID    VARCHAR(2000)
      ,@whereAlbumID    VARCHAR(2000)
      ,@wherePictureID VARCHAR(2000)
AS
BEGIN
     SET NOCOUNT ON;
 
    WITH Results1 AS
        (
        SELECT 
            StoryID,
            AlbumID,
            StoryTitle,
            [AlbumName] = NULL,
            [AlbumCover] = 
                (
                SELECT URL 
                FROM AlbumPictures 
                WHERE (AlbumID = dbo.Stories.AlbumID) AND (AlbumCover = 'True')
                ),
            Votes,
            [PictureId] = NULL,
            [tableName] = 'stories',
            [Sort] = NEWID()
        FROM Stories 
        WHERE 
                StoryID IN 
                (
                SELECT StringVal 
                FROM funcListToTableInt(@whereStoryID)
                )
        )
    , Results2 AS
        (
        SELECT    
            [StoryID] = NULL ,
            AlbumID,
            [StoryTitle] NULL,
            AlbumName,
            [AlbumCover] = 
                (
                SELECT URL 
                FROM AlbumPictures AS AlbumPictures_3 --<<<DO YOU NEED THIS ALIAS?
                WHERE (AlbumID = Albums.AlbumID) AND (AlbumCover = 'True')
                ),
            Votes,
            [PictureId] = NULL,
            [tableName] = 'albums',
            [Sort] = NEWID()
        FROM Albums 
        WHERE 
            AlbumID IN 
                (
                SELECT StringVal 
                FROM funcListToTableInt(@whereAlbumID)
                )
        )       
    , Result3 AS  
        (
        SELECT 
            [StoryID] = NULL, 
            [AlbumID] = NULL,
            [StoryTitle] = NULL,
            [AlbumName] = NULL,
            URL,
            Votes,
            PictureID,
            [tableName] = 'pictures',
            [Sort] = NEWID()
        FROM AlbumPictures --AS AlbumPictures_1 <<<DO YOU NEED THIS ALIAS?
        WHERE 
            PictureID IN 
                (
                SELECT StringVal 
                FROM funcListToTableInt(@wherePictureID)
                )
        )
    , Result4 AS  
        (
        SELECT * FROM Results1 UNION ALL
        SELECT * FROM Results2 UNION ALL
        SELECT * FROM Results3
        )
    , Results AS
        (
        SELECT 
                [RowNumber] = ROW_NUMBER() OVER (ORDER BY [Sort] DESC),
            x.* 
        FROM Results4   x
        )
 SELECT * 
 FROM Results
 WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1;
 DECLARE @RecordCount INT = @@RowCount; 
 
 SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)));
 
END
Posted 30-Dec-12 9:33am
Updated 30-Dec-12 12:28pm
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

It's almost always best to not use it. But, insofar as there is a 'correct' way, union all will merge the results of several selects, and keep duplicates ( union without all ignores duplicates ). This will work with this SQL and is thus 'correct'
  Permalink  
Comments
Member 7781963 30-Dec-12 18:13pm
   
Is there any way i can skip the procedure and do the same thing in dynamic sql?
Christian Graus 30-Dec-12 18:17pm
   
Just get rid of the temp tables and do union all on the SQL statements that create the tables. The temp tables are not needed.
Member 7781963 30-Dec-12 18:19pm
   
you mean remove #Results1,#Results2 and #Results3 ?
Christian Graus 30-Dec-12 18:24pm
   
Yes, unless you need them to add the row numbers, they are worthless. If you do need them, then you probably need a proc. I'd do everything I could in a proc any how, esp something this convoluted. It just makes it easier to read/edit.
Member 7781963 30-Dec-12 18:29pm
   
i have added a new solution that i am trying now...is that a better solution than the earlier one...and yes i need the row numbers.
Christian Graus 30-Dec-12 18:38pm
   
If this works, it looks better. Does the , equate to Union All ? I don't know, I'd not seen that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 30 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100