Supermarket Logistics Management Database Design

数据库设计报告

存储过程：查询销售记录

CREATE PROCEDURE [dbo].search_sell_rec 
    @id varchar(8), 
    @name varchar(30), 
    @time varchar(30), 
    @mid varchar(8) 
AS 
    IF (@mid = '') 
    BEGIN 
        SELECT P.p_id AS pid, 
               P.p_name AS pname, 
               P.p_scale AS pscale, 
               S.s_qty AS sqty, 
               S.s_price AS sprice, 
               S.s_time AS stime, 
               E.e_name AS ename 
        FROM product P, sell S, employee E 
        WHERE P.p_id LIKE '%' + @id + '%' 
          AND P.p_name LIKE '%' + @name + '%' 
          AND S.s_time LIKE '%' + @time + '%' 
          AND S.p_id = P.p_id 
          AND S.e_id = E.e_id 
        ORDER BY S.s_id DESC 
    END 
    IF (@mid != '') 
    BEGIN 
        SELECT P.p_id AS pid, 
               P.p_name AS pname, 
               P.p_scale AS pscale, 
               S.s_qty AS sqty, 
               S.s_price AS sprice, 
               S.s_time AS stime, 
               E.e_name AS ename, 
               P.p_qty AS pqty 
        FROM product P, sell S, employee E 
        WHERE P.p_id LIKE '%' + @id + '%' 
          AND P.p_name LIKE '%' + @name + '%' 
          AND S.s_time LIKE '%' + @time + '%' 
          AND S.m_id = @mid 
          AND S.p_id = P.p_id 
          AND S.e_id = E.e_id 
        ORDER BY S.s_id DESC 
    END 
GO