proceduredemo10.sql

T-SQL示例大全

/* 文件名称: ProcedureDemo10.sql */
USE 北风贸易
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.EmployeeSaleByYear') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.EmployeeSaleByYear
GO

CREATE PROCEDURE dbo.EmployeeSaleByYear
@Name nvarchar(20),
@SaleYear int = 1996
AS
IF (SELECT COUNT(*) FROM 员工 WHERE 姓名 = @Name) > 0
      SELECT a.员工编号, a.姓名,
                    年份 = @SaleYear,
                    销售总金额 = SUM(c.单价 * c.数量*(1 - c.折扣))
        FROM 员工 a INNER JOIN 订货主档 b ON a.员工编号 = b.员工编号
                               INNER JOIN 订货明细 c ON b.订单号码 = c.订单号码
        WHERE 姓名 = @Name AND YEAR(b.订单日期) = @SaleYear
        GROUP BY a.员工编号, a.姓名
ELSE
      SELECT 警示 = '不存在姓名为 ' + @Name + ' 的员工资料'

GO