存储过程第三讲：sp实战

/****** Object:  StoredProcedure [dbo].[rp_me_step_rfid]    Script Date: 09/03/2012 11:57:25 ******/
IF OBJECT_ID('test_spl','P') IS NULL
BEGIN
	EXEC ('CREATE PROCEDURE test_spl AS BEGIN SELECT 1; END');
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[test_spl]
(
	@wo_nbr		     VARCHAR(50),		 
	@acctid          INT,
	@i_emp_id         VARCHAR(30),          
	@o_res            VARCHAR(100) OUTPUT   ---程序返回消息
)

AS
BEGIN TRY
	SET ANSI_WARNINGS OFF;
	SET NOCOUNT ON;
	
	--调试使用,发布时需要注释掉	
	--DECLARE @wo_nbr VARCHAR(50) ;
    --DECLARE @acctid INT;
    --DECLARE @i_emp_id VARCHAR(30);
    --DECLARE @o_res VARCHAR(100);
	--SET @wo_nbr = 'WCURRY007AA';
	--SET @acctid = 1;
	--SET @i_emp_id = '00000001';
	--SET @o_res = '';


    --此处放sql代码逻辑
	
    
    SET @o_res = 'OK';
  
    
	
END TRY
-- TRY CATCH 捕获异常
BEGIN CATCH
DECLARE 
	@ErrorMessage NVARCHAR(4000),
	@ErrorSeverity INT,
 	@ErrorState INT;
	SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();	
		
	RAISERROR 
	(
	 	@ErrorMessage, -- Message text.
		@ErrorSeverity, -- Severity.
		@ErrorState -- State.
	);
	
	SET @o_res = @ErrorMessage;

END CATCH

GO