常用的擷取最大值

-------------------表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Code]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Code]
GO

CREATE TABLE [dbo].[Code] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [codenumber] [bigint] NULL ,
 [info] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

 

----------------預存程序

/*
這個預存程序主要是用來產生各種各樣的單號的。傳入一個特別碼單號來分清他對應的是那一個類型單號的最大值
*/
create        PROCEDURE [dbo].[addmaxsto] @Code varchar(20) ,@ouototal varchar(50)='' output AS
declare @orderidcard varchar(14)
declare @temp varchar(50)
if (select  left(codenumber,8)  from  code where code=@Code)=(select convert(varchar(8),getdate(),112))--如果是同一天
  begin
    UPDATE code SET codenumber =codenumber +1 where code=@Code    --在原來的單號上加1
  end
else
  begin
     set @orderidcard=(cast((select convert(varchar(8),getdate(),112))as varchar(50)))+'00001' --產生單的編號
     UPDATE code SET codenumber =@orderidcard where code=@Code
  end

 select top 1 @ouototal=codenumber   from (select * from  code where code=@Code)  

output

 

GO