SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Insus.NET>
-- Create date: <Create Date,,2025-11-22>
-- Description: <Description,,获取媒体数据,并进行数据分页>
-- =============================================
CREATE PROCEDURE [dbo].[usp_Media_GetByCatalogKey]
(
@Catalog_nbr INT,
@PageIndex INT,
@PageSize INT
)
as
BEGIN
DECLARE @result TABLE
(
[ID] INT,
[FileName] NVARCHAR(255),
SystemName NVARCHAR(255),
[Width] INT,
[Height] INT,
[OriginalWidth] INT,
[OriginalHeight] INT,
[OriginalImage_Path] NVARCHAR(255),
[Thumbnail_Path] NVARCHAR(255)
)
INSERT INTO @result
(
[ID],
--...
[Thumbnail_Path]
)
SELECT TOP (SELECT @PageSize)
[Media_nbr],
[FileName],
[SystemName],
[Width], --缩略图宽度
[Height], --缩略图高度
[OriginalWidth], --图宽度
[OriginalHeight], --原图高度
'/'+ [Media_Directory] +'/'+ [Storage_Directory]+'/O/'+ [FileName], --组合原图路径
'/'+ [Media_Directory] +'/'+ [Storage_Directory]+'/T/'+ [SystemName] --组合缩略图路径
FROM (
SELECT row_number() OVER(ORDER BY [Media_nbr]) AS rownumber,
[Media_nbr],
--...
[OriginalHeight]
FROM [dbo].[Media]
WHERE [Catalog_nbr] = @Catalog_nbr
) temp_row
WHERE rownumber > (@PageIndex - 1) * @PageSize;
SELECT
[ID],
--...
[Thumbnail_Path]
FROM @result
END