这是在存储过程中使用 UNION ALL 的正确方法吗? [英] Is this the correct way to use UNION ALL in a stored procedure?
本文介绍了这是在存储过程中使用 UNION ALL 的正确方法吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是在存储过程中UNION ALL 的正确方法吗?
Is this the correct way to UNION ALL in a stored procedure?
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
推荐答案
这些天我喜欢使用非物化的 CTEs 而不是临时表 - 尽管在某些情况下(比如数据需要一个index) 我将使用临时表.
These days I like to use non-materialized CTEs rather than temp tables - although in certain circumstances (say the data needs an index) I'll use temp tables.
主要是很多装饰性的东西我真的会改变，希望在未来让它更具可读性(这没有经过测试，因为我没有你的数据的副本)
Mainly lots of cosmetic stuff I'd change really all in the way of hopefully making it more readable in the future (this is not tested as I do not have a copy of your data)
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
我通常使用 Aaron Bertrand 的建议来编写存储过程，这篇博文是我的清单和模板，我用来尝试统一我在所有 Sproc 中使用的样式:
I'm generally use Aaron Bertrand's suggestions for writing Stored Procedures this blog post is my checklist and the template I use to try to unify the style I use with all my Sprocs:
https://sqlblog.org/2008/10/30/my-stored-procedure-best-practices-checklist
我认为正如 Gordon 建议的那样，您可以将大量逻辑移出存储过程并创建一个 VIEW，如下所示:
I think as Gordon suggested you could move lots of the logic out of the stored procedure and create a VIEW like this:
CREATE VIEW [console].[vw_mySimpleView]
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
)
, Results2 AS
(
SELECT
[StoryID] = NULL ,
AlbumID,
[StoryTitle] NULL,
AlbumName,
[AlbumCover] =
(
SELECT URL
FROM AlbumPictures
WHERE (AlbumID = Albums.AlbumID) AND (AlbumCover = 'True')
),
Votes,
[PictureId] = NULL,
[tableName] = 'albums',
[Sort] = NEWID()
FROM Albums
)
, Result3 AS
(
SELECT
[StoryID] = NULL,
[AlbumID] = NULL,
[StoryTitle] = NULL,
[AlbumName] = NULL,
URL,
Votes,
PictureID,
[tableName] = 'pictures',
[Sort] = NEWID()
FROM AlbumPictures
)
, Result4 AS
(
SELECT * FROM Results1 UNION ALL
SELECT * FROM Results2 UNION ALL
SELECT * FROM Results3
)
SELECT *
FROM Results4;
GO
那么 Sproc 会短很多:
Then the Sproc would be a lot shorter:
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 *
FROM
(
SELECT
[RowNumber] = ROW_NUMBER() OVER (ORDER BY [Sort] DESC),
x.*
FROM
(
SELECT *
FROM [dbo].[vw_mySimpleView]
WHERE
StoryID IN
(
SELECT StringVal
FROM funcListToTableInt(@whereStoryID)
)
OR
AlbumID IN
(
SELECT StringVal
FROM funcListToTableInt(@whereAlbumID)
)
) x
)
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
这篇关于这是在存储过程中使用 UNION ALL 的正确方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
