new_no.rar_Noé 1_流水号

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zhw_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[zhw_test] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tbl_test] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_xh]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tbl_xh] GO CREATE TABLE [dbo].[tbl_test] ( [ID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL , [AA] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[tbl_xh] ( [LB] [varchar] (4) COLLATE Chinese_PRC_CI_AS NOT NULL , [ID] [decimal](18, 0) NULL , [XH] [int] NULL , [Rq] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO CREATE PROCEDURE zhw_test( @ckdh varchar(10) out ) AS declare @xxh decimal, @yxh decimal, @xh int, @tc varchar(1), @yrq varchar(10), @xrq varchar(10) insert into tbl_test(aa) values('abc') select @xxh=@@identity --表中最大值 delete tbl_test select @tc=1 while @tc=1 begin select @yxh=id,@xh=xh,@yrq=rq from tbl_xh where lb='0101' select @xrq=convert(char(10),getdate(),120) if @xxh=@yxh+1 or @yxh is null begin if @xh is null select @xh=1 else if substring(@yrq,1,7)=substring(@xrq,1,7) select @xh=@xh+1 else select @xh=1 select @tc=0 begin tran update tbl_xh set xh=@xh,id=@xxh,rq=@xrq where lb='0101' commit select @ckdh=@xh end end --select @ckdh as xh from tbl_test GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO