proceduredemo2.sql

T-SQL示例大全

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

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


CREATE PROCEDURE dbo.SalaryRange
AS
DECLARE @msg varchar(255)
IF (SELECT COUNT(*) FROM 飞狐工作室
         WHERE 姓名 LIKE '许%' AND
                       目前薪资 BETWEEN 15000 AND 20000) > 0
    BEGIN
     SET NOCOUNT ON
     SET @msg =
             '有一些 ''许'' 姓员工的薪资介于 15000 ～ 20000。'+
             '这些员工是：'
     PRINT @msg
     PRINT REPLICATE('-',40)

     SELECT 姓名,目前薪资 FROM 飞狐工作室
       WHERE 姓名 LIKE '许%' AND
                     目前薪资 BETWEEN 15000 AND 20000
    END
ELSE
    BEGIN
     SET @msg = '没有任何 ''许'' 姓员工的薪资介于'+
                ' 15000 ° 20000