USE [DBName]
GO
/****** Object: StoredProcedure [dbo].[sp_GetNo] Script Date: 10/24/2013 19:26:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author, XiaoPan.Gong>
-- Create date: <Create Date, 2013-10-23>
-- Description: <Description, Upload Special Event Amout>
-- =============================================
/* 第一
CREATE TABLE [dbo].[T_Saler](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SO_No] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
[Sale_Num] [int] NULL,
[Remark] [nvarchar](max) NULL
)
--执行
Declare @Get_No nvarchar(50)
GO
/****** Object: StoredProcedure [dbo].[sp_GetNo] Script Date: 10/24/2013 19:26:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author, XiaoPan.Gong>
-- Create date: <Create Date, 2013-10-23>
-- Description: <Description, Upload Special Event Amout>
-- =============================================
/* 第一
CREATE TABLE [dbo].[T_Saler](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SO_No] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
[Sale_Num] [int] NULL,
[Remark] [nvarchar](max) NULL
)
--执行
Declare @Get_No nvarchar(50)
exec [dbo].[sp_GetNo] 'T_Saler','SO_No',10,8,'SN',@Get_No output
print @Get_No
SONO2013102400000001
*/
ALTER PROCEDURE [dbo].[sp_GetNo]
(
@table_name nvarchar(50),--表名
@field nvarchar(50),--字段名
@left int,--左边截取的长度
@right int,--右边截取的长度
@split nvarchar(10),--编号其实字母或字符串
@return_no nvarchar(50) output --返回的编号
)
AS
BEGIN
Declare @sql nvarchar(MAX)
Declare @rightnum int
Declare @splitstr nvarchar(50)
Declare @rightstr nvarchar(10)
Declare @rightint int
Declare @tbno nvarchar(50)
Set @rightint=@right
Set @rightstr=''
while @rightint>0
begin
Set @rightstr=@rightstr+'0'
Set @rightint=@rightint-1
end
Set @splitstr=@split+CONVERT(nvarchar,GETDATE(),112)
Set @sql='select @rightnum=MAX(Convert(int,right(Convert(nvarchar,isnull('+@field+',0)),'+Convert(nvarchar,@right)+'))) from '
+@table_name+' where left('+@field+','+Convert(nvarchar,@left)+')='+''''+@splitstr+''''
SONO2013102400000001
*/
ALTER PROCEDURE [dbo].[sp_GetNo]
(
@table_name nvarchar(50),--表名
@field nvarchar(50),--字段名
@left int,--左边截取的长度
@right int,--右边截取的长度
@split nvarchar(10),--编号其实字母或字符串
@return_no nvarchar(50) output --返回的编号
)
AS
BEGIN
Declare @sql nvarchar(MAX)
Declare @rightnum int
Declare @splitstr nvarchar(50)
Declare @rightstr nvarchar(10)
Declare @rightint int
Declare @tbno nvarchar(50)
Set @rightint=@right
Set @rightstr=''
while @rightint>0
begin
Set @rightstr=@rightstr+'0'
Set @rightint=@rightint-1
end
Set @splitstr=@split+CONVERT(nvarchar,GETDATE(),112)
Set @sql='select @rightnum=MAX(Convert(int,right(Convert(nvarchar,isnull('+@field+',0)),'+Convert(nvarchar,@right)+'))) from '
+@table_name+' where left('+@field+','+Convert(nvarchar,@left)+')='+''''+@splitstr+''''
EXEC SP_EXECUTESQL @sql, N'@rightnum int OUTPUT', @rightnum OUTPUT
if @rightnum is null
begin
Set @rightnum=0
end
Declare @rightlen int
Declare @rightlenght int
Set @rightlen=LEN(Convert(nvarchar,@rightnum))
Set @rightnum=@rightnum+1
Set @rightlenght=LEN(Convert(nvarchar,@rightnum))
if @rightlen=@rightlenght
begin
Set @tbno=RIGHT(@rightstr,@right-@rightlen)+Convert(nvarchar,@rightnum)
end
else
begin
Set @tbno=RIGHT(@rightstr,@right-@rightlenght)+Convert(nvarchar,@rightnum)
end
Set @return_no=@splitstr+ISNULL(@tbno,LEFT(@rightstr,@right-1)+'1')
select @return_no AS Return_No
if @rightlen=@rightlenght
begin
Set @tbno=RIGHT(@rightstr,@right-@rightlen)+Convert(nvarchar,@rightnum)
end
else
begin
Set @tbno=RIGHT(@rightstr,@right-@rightlenght)+Convert(nvarchar,@rightnum)
end
Set @return_no=@splitstr+ISNULL(@tbno,LEFT(@rightstr,@right-1)+'1')
select @return_no AS Return_No
END
SET ANSI_NULLS OFF