createusrproc.sql

来自「T-SQL示例大全」· SQL 代码 · 共 46 行

USE 北风贸易
GO

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

CREATE PROCEDURE dbo.AvgSalaryAge
AS
SELECT 部门,平均薪资 = AVG(目前薪资),平均年龄 = AVG(DATEDIFF(yy,出生日期,GETDATE()))
    FROM 北风贸易.dbo.飞狐工作室
    GROUP BY 部门

GO

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

CREATE PROCEDURE dbo.CityPeople
@Address nvarchar(41)= '%'
AS
SELECT 姓名,年龄 = DATEDIFF(yy,出生日期,GETDATE()),家庭地址
    FROM 北风贸易.dbo.飞狐工作室
    WHERE 家庭地址 LIKE @Address
GO

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

CREATE PROCEDURE dbo.DepartmentSalaryInfo
     @department varchar(10),
     @average money OUTPUT,
     @maximum money OUTPUT,
     @minimum money OUTPUT
AS
SELECT @average = AVG(目前薪资),
               @maximum = MAX(目前薪资),
               @minimum = MIN(目前薪资)
     FROM 飞狐工作室
     WHERE 部门 = @department

GO