这是UNION ALL
存储过程中的正确方法吗?
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
如今,我喜欢使用非具体化CTE
的而不是临时表-尽管在某些情况下(例如数据需要索引),我将使用临时表。
我主要会改变很多化妆品的东西,以期将来使它更具可读性 (由于我没有您的数据的副本,因此未经过测试)
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所用样式的模板:
https://sqlblog.org/2008/10/30/my-stored-procedure-best-practices-
checklist
我认为,正如Gordon所建议的那样,您可以将许多逻辑从存储过程中移出,并创建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
然后,Sproc会短很多:
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
问题内容: 我能够使用三个链接来组合一个简化的完整History.js示例,以从整个页面加载内容片段,而无需更新页面和更新浏览器历史记录。 这是相关的代码段- 完整的工作示例在此处http://jsfiddle.net/PT7qx/show 我想知道这是否正确。以前的版本可以使用#url绑定到事件。我没有看到使用此最新版本将事件绑定到url的任何示例,因此我使用了.on()click事件来调用Hi
问题内容: 我读这个和这个,结果发现,在课堂上我需要保存到类的引用,当东西在发生类,我们执行由类的接口中定义的方法工具。好吧,我明白了。我以不同的方式使用接口来调用回调: 我发现这种方式更具可读性,更容易理解,但是不确定这是否是不好的做法。因此,我可以随意走这条路吗? 问题答案: 所以我可以随意走这条路吗? 这种方式不是很正确。 您在说的是称为Observer或/ 模式。 简而言之:订户想要从发布
我读了这篇文章和这篇文章,发现在类中,我需要保存对类的引用,当类中发生一些事情时,我们执行一个由类实现的接口定义的方法。不知何故我明白了。我以不同的方式使用接口来调用回调: 我发现这种方式更易读,更容易理解,但不确定这是否是一个不好的做法。所以我可以自由地走得更远吗?
问题内容: 我试图在postgres 9.3上使用sql调用函数内的函数。 这个问题与我的另一篇文章有关。 我写了下面的函数。到目前为止,我还没有合并任何类型的save-output(COPY)语句,因此我试图通过创建嵌套函数print-out函数来解决此问题。 以上功能有效。 尝试创建嵌套函数。 调用嵌套函数。 输出 上面给出了这个。但是,当在print_out()中将arg1,arg2替换为’
问题内容: 我在任何地方都找不到此答案,但是可以从MySQL中的另一个存储过程调用存储过程吗?我想找回标识值,并在父存储过程中使用它。我们不能再使用FUNCTIONS! 问题答案: 参数应该可以帮助您将值返回给调用过程。基于此,解决方案必须是这样的。
问题内容: 我知道我不应该改变输入,而应该克隆对象来改变它。我遵循在redux入门项目上使用的约定,该约定使用: 用于添加项目-我使用了点差将项目追加到数组中。 用于删除我使用的: 但这改变了输入状态对象-即使我要返回一个新对象,也禁止这样做吗? 问题答案: 不,永远不要改变你的状态。 即使您要返回一个新对象,您仍然会污染旧对象,而您从来不希望这样做。这使得在旧状态与新状态之间进行比较时会出现问题