一。第一种方式
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