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
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
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