SQL存储过程生成顺序编码

攻城狮宇轩技术 2022年11月15日

0 收藏 659 点赞 2,413 浏览 1071 个字

一。第一种方式

USE [WJKC]
GO
/****** Object: StoredProcedure [dbo].[Address_GetCode1]    Script Date: 2016/3/29 星期二下午 10:20:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:       <Author,,Name>
— Create date: <Create Date,,>
— Description:   <Description,,>
— =============================================
ALTER PROCEDURE [dbo].[Address_GetCode1]
   — Add the parameters for the stored procedure here
@CODE nvarchar(20) output–@DZ nvarchar(250),@ISTY int,@DETAIL nvarchar(250)
AS
BEGIN
–取出最大CODE
SET @CODE=NULL
SELECT TOP 1 @CODE=CODE from SHDZ
ORDER BY CODE DESC
–如果没取出来
    IF @CODE IS NULL
   BEGIN
    SET @CODE=’01’
   END
    ELSE
             BEGIN
           DECLARE @NUM VARCHAR(2)
            –取出编号+1
            SET @NUM=CONVERT(VARCHAR,(CONVERT(INT,RIGHT(@CODE,2))+1))

        SET @NUM=REPLICATE(‘0’,2-LEN(@NUM))+@NUM

        SET @CODE=@NUM
END
END

二，第二种方式

SQL存储过程主要语句：

定义：MyID varchar(4)=null

……
……
select @MyID=Max(MyID)from MyTable–获取最大编号
if(@MyID is null)
set @MyID=’0001′–生成自动编号
else
set @MyID=cast(cast(substring(@MyID,1,4) as int)+1 as varchar(4)–编号自增1