sqlserver2005 存储过程模板及调用

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

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

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_@R_772_10930@or @R_772_10930@OR LOCAL FORWARD_ONLY KEYSET SCROLL_LOCKS FOR SELEct ep_name,belong_sepa,processinstid,orgid from ENTERPRISE where ep_id = @epId;    
    OPEN order_@R_772_10930@or
    --开始循环游标变量
    FETCH NEXT FROM order_@R_772_10930@or INTO @epName,@belongSepa,@processinstid,@orgid;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    insert into BAK_OLD_EPINFO values (@epId,@epName,@orgid,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_@R_772_10930@or INTO @epName,@orgid;    
    END
    CLOSE order_@R_772_10930@or
    DEALLOCATE order_@R_772_10930@or
    SET transaCTION isolation LEVEL READ COMMITTED;
END@H_801_9@ 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER PROCEDURE [dbo].[deleteAMANDTPByAMID] 
    -- Add the parameters for the stored procedure here
    @amId varchar(20),@resValue varchar(2) OUTPUT
    --0 成功 1 失败 2 计划在审批
AS
BEGIN
    declare @tpid varchar(20);
    declare @epidcs varchar(20);
    declare @epidcz varchar(20);
    declare @processid numeric(18,0);
    SET NOCOUNT ON;
    SET transaCTION isolation LEVEL seriaLIZABLE;
    BEGIN
        SELEct @tpid=tp_id,@epidcs=en_id_cs,@epidcz=en_id_cz,@processid=PROCESSINSTID from TRANSFER_PLAN where am_id=@amId;
        if (@processid is not null)
        BEGIN
            set @resValue = '2';
        END
        ELSE
        BEGIN
            insert BAK_OLD_AMTP (am_id,tp_id,ep_id_cs,ep_id_cz,sysdate) values (@amid,@tpid,@epidcs,@epidcz,getdate());
            if (@@ERROR=0)
            BEGIN
                set @resValue = '0';
                update AGREEMENT set status='1' where am_id=@amId;
                update TRANSFER_PLAN set status='1' where am_id=@amId;
            END
            ELSE
            BEGIN
                SET @resValue= '1'; --失败
            END
        END
    END
    SET transaCTION isolation LEVEL READ COMMITTED;
END@H_801_9@ 
如何调用的语句例子
 
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;@H_801_9@