proceduredemo18.sql

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

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

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

CREATE PROCEDURE dbo.fox_cursor_proc
@department nvarchar(10),
@address nvarchar(41) = '%',
@employee_cursor CURSOR VARYING OUTPUT
AS
SET @employee_cursor = CURSOR SCROLL DYNAMIC
FOR
SELECT 姓名,
               性别 = CASE 员工性别
                           WHEN 0 THEN '女'
                           WHEN 1 THEN '男'
                           END,
               婚姻 = CASE 婚姻状况
                           WHEN 0 THEN '未婚'
                           WHEN 1 THEN '已婚'
                           END,
               电话号码, 家庭地址
   FROM 飞狐工作室 WHERE 部门 = @department AND 家庭地址 LIKE @address

OPEN @employee_cursor

GO