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_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

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

如何调用的语句例子

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;