创建proc_video_search存储过程实现视频查询及调用示例

Got it, let's build this stored procedure exactly as you need it. I'll assume you're working with SQL Server (since the EXEC syntax matches), and that you have three core tables: Videos (holds video details like Name), Copies (tracks video copies with CopyID, Format, Status), and Loans (records loan history with LoanDate, DueDate, and ReturnDate to identify unreturned copies).

Create the proc_video_search Stored Procedure #

CREATE PROCEDURE proc_video_search
    @VideoName NVARCHAR(100),
    @Format NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        v.Name AS [视频名称],
        c.CopyID AS [副本ID],
        c.Format AS [格式],
        c.Status AS [副本状态],
        -- 仅展示未归还副本的借出和到期日期
        CASE WHEN l.ReturnDate IS NULL THEN l.LoanDate ELSE NULL END AS [借出日期],
        CASE WHEN l.ReturnDate IS NULL THEN l.DueDate ELSE NULL END AS [到期日期]
    FROM
        Videos v
    JOIN
        Copies c ON v.VideoID = c.VideoID
    LEFT JOIN
        Loans l ON c.CopyID = l.CopyID
    WHERE
        -- 匹配视频名称（模糊匹配，可根据需求调整为精确匹配）
        v.Name LIKE '%' + @VideoName + '%'
        -- 匹配格式（允许空值，若传入NULL则不限制格式）
        AND (@Format IS NULL OR c.Format = @Format)
        -- 排除损坏状态的副本
        AND c.Status <> 'D'
    ORDER BY
        v.Name, c.CopyID;
END
GO

Key Logic Breakdown #

• Parameter Flexibility: The procedure takes two parameters: @VideoName (uses wildcard matching to find videos with the keyword in their name) and @Format (if you pass NULL, it will return all formats instead of filtering).
• Unreturned Copy Handling: We use a LEFT JOIN with the Loans table and check for ReturnDate IS NULL to spot unreturned copies. The CASE statements ensure loan details only show up for these copies.
• Damage Filter: We explicitly exclude any copies marked as 'D' (damaged) per your requirement.
• Sorting: Results are ordered first by video name, then by copy ID to keep the output clean and organized.

Example Execution #

EXEC proc_video_search('ANOTHER', 'DVD');

This call will return all DVD-format copies of videos containing "ANOTHER" in their name, skip any damaged copies, and display loan dates for any unreturned items.

内容的提问来源于stack exchange，提问作者Irma