Click here to Skip to main content
11,505,744 members (58,745 online)
Rate this: bad
good
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
Edited 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 at 30-Dec-12 18:13pm
   
Is there any way i can skip the procedure and do the same thing in dynamic sql?
Christian Graus at 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 at 30-Dec-12 18:19pm
   
you mean remove #Results1,#Results2 and #Results3 ?
Christian Graus at 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 at 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 at 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
0 Sergey Alexandrovich Kryukov 532
1 F-ES Sitecore 380
2 Richard Deeming 270
3 CHill60 245
4 Richard MacCutchan 230
0 Sergey Alexandrovich Kryukov 1,087
1 Maciej Los 625
2 Peter Leow 540
3 F-ES Sitecore 525
4 OriginalGriff 487


Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 30 Dec 2012
Copyright © CodeProject, 1999-2015
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