sqlserver2005 存储过程模板及调用

浏览数：103 / 时间：2015年06月12日

本模板主要提供快速创建一个存储过程

本例子中包含：循环游标，事务

USE [数据库名称]
GO
/******    脚本日期: 11/25/2014 01:05:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [用户].[存储过程名称] 
    @epId varchar(20),
    @bizname varchar(150),
    @resValue varchar(2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    declare @epName varchar(500);
    declare @belongSepa varchar(6);
    declare @processinstid numeric(18, 0);
    declare @orgid numeric(10, 0);
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        set @resValue = 0;
    declare order_cursor CURSOR LOCAL FORWARD_ONLY KEYSET SCROLL_LOCKS FOR select ep_name,belong_sepa,processinstid,orgid from ENTERPRISE where ep_id = @epId;    
    OPEN order_cursor
    --开始循环游标变量
    FETCH NEXT FROM order_cursor INTO @epName,@belongSepa,@processinstid,@orgid;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    insert into BAK_OLD_EPINFO values (@epId,@epName,@processinstid,@orgid,@belongSepa,getdate());
    if (@@ERROR=0)
    BEGIN
        SET @resValue=0; --成功
        delete from ENTERPRISE where ep_id = @epId;
    END
    ELSE
    BEGIN
        SET @resValue=1; --失败
    END
    FETCH NEXT FROM order_cursor INTO @epName,@belongSepa,@processinstid,@orgid;    
    END
    CLOSE order_cursor
    DEALLOCATE order_cursor
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END

如何调用的语句例子

declare @epid varchar(50);
declare @epname varchar(500);
declare @resvalue varchar(500);
set @epid = ‘123456‘;
set @epname = ‘xxxxxx‘;
exec [用户].[存储过程名称] @epid,@epname,@resvalue output
print @resvalue;