这些天我喜欢使用非物化CTE
s 而不是临时表 - 尽管在某些情况下(比如数据需要索引)我将使用临时表。
主要是我会改变很多装饰性的东西,希望将来让它更具可读性(这未经测试,因为我没有您的数据副本)
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 编写存储过程的建议,这篇博文是我的清单和我用来尝试统一我与所有存储过程使用的样式的模板:
https://sqlblog.org/2008/10/30/my-stored-procedure-best-practices-checklist https://sqlblog.org/2008/10/30/my-stored-procedure-best-practices-checklist
我认为正如戈登建议的那样,您可以将大量逻辑从存储过程中移出并创建一个VIEW
像这样:
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
那么存储过程就会短很多:
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