sqlserver基本的脚本规范_代码小牛的博客_sqlserver 脚本

1. 存储过程

必须判断是否存在,先drop然后在create

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PROC_INS_SETTLE_ACCOUNT') AND type in (N'P', N'PC'))
DROP PROCEDURE PROC_INS_SETTLE_ACCOUNT
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure PROC_INS_SETTLE_ACCOUNT 

CREATE  procedure [dbo].[PROC_INS_SETTLE_ACCOUNT]  
	@sJgbm VARCHAR(22), 
	@sRzlsh VARCHAR(64), 
	@sCzlsh VARCHAR(64), 
	@sRybm VARCHAR(64), 
	@sRyxm VARCHAR(64),
	@sJslsh VARCHAR(64)
AS  
BEGIN  
   
   BEGIN TRAN  
   BEGIN TRY  
	  --查询数据
     while exists(select *from  SYS_GNCD where XXX='1')
        begin
	      select *from  SYS_GNCD
        end
	 
   END TRY  
   BEGIN CATCH  
   IF @@TRANCOUNT>0  
   BEGIN  
     ROLLBACK TRAN   
     select 'F' as [STATUS], '结算失败!'+'失败原因:'+ERROR_MESSAGE() as MSG  
   END  
  
   END CATCH  
   IF @@TRANCOUNT>0  
   begin  
    COMMIT TRAN    
    select 'T' as [STATUS],'结算成功!' as MSG   
   end   
  
endIF EXISTS (