¿Es esta la manera correcta de usar UNION ALL en un procedimiento almacenado?

¿Es esta la manera correcta de UNION ALL en un procedimiento almacenado?

 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 

En estos días me gusta utilizar CTE s no materializadas en lugar de tablas temporales, aunque en ciertas circunstancias (digamos que los datos necesitan un índice) usaré tablas temporales.

Principalmente muchas cosas cosméticas que cambiaría realmente en el path de la esperanza de hacerlo más legible en el futuro (esto no está probado ya que no tengo una copy de sus datos)

 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 

Generalmente utilizo las sugerencias de Aaron Bertrand para escribir Procedimientos almacenados, esta publicación de blog es mi list de verificación y la plantilla que uso para tratar de unificar el estilo que uso con todos mis Sprocs:

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/10/30/my-stonetworking-procedure-best-practices-checklist.aspx


Creo que cuando Gordon sugirió que podía sacar mucha lógica del procedimiento almacenado y crear una VIEW así:

 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 

Entonces, el Sproc sería mucho más corto:

 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 

Aquí hay algunos comentarios:

(1) Prefiero las variables con valores de tabla ( declare @Results as table . . . ) en lugar de tablas temporales.

(2) En general, probablemente sea mejor escribir una sola consulta en lugar de consultas separadas. Por lo tanto, puede eliminar las tablas de resultados intermedios de todos modos. Los motores SQL están diseñados para optimizar las routes de ejecución. Dales la oportunidad de trabajar. Dicho esto, a veces se equivocan y las tablas intermedias son deseables / necesarias.

(3) Tu género está bien, pero debes ser cuidadoso. Si Sort tiene valores duplicates, corre el riesgo de get valores repetidos y diferentes iteraciones causarán problemas.

(4) Dado que realmente solo está devolviendo resultados de una consulta, ¿por qué no simplemente define la consulta (quizás como una vista) y elimina por completo el procedimiento almacenado? El procedimiento almacenado hace que sea poco probable que SQL Server guarde en caching los resultados con fines de pagination.

(5) También me pregunto si puede eliminar las llamadas de function en la cláusula from , ya que también pueden afectar negativamente el performance.