 加好友  发短信
等级：四尾狐
帖子：948
积分：7861
威望：0
精华：0
注册：2013/7/7 13:37:00
|
 Post By：2014/12/19 10:24:00 [只看该作者]
用到的表代码： USE [编号测试]
GO
/****** 对象: Table [dbo].[编号] 脚本日期: 10/14/2014 10:21:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[编号](
[_Identify] [int] IDENTITY(1,1) NOT NULL,
[_Locked] [bit] NULL,
[_SortKey] [numeric](28, 14) NULL,
[前缀] [nvarchar](25) NULL,
[顺序号] [int] NULL,
[表名] [nvarchar](50) NULL,
[已用标识] [bit] NULL,
[日期] [datetime] NULL,
CONSTRAINT [PrimaryKey_编号] PRIMARY KEY NONCLUSTERED
(
[_Identify] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [编号测试]
GO
/****** 对象: Table [dbo].[费用记录单] 脚本日期: 10/14/2014 10:22:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[费用记录单](
[_Locked] [bit] NULL,
[_SortKey] [decimal](18, 0) NULL,
[制单人] [nvarchar](16) NULL,
[制单时间] [datetime] NULL,
[费用单号] [nvarchar](255) NULL,
[_Identify] [int] IDENTITY(1,1) NOT NULL,
[收费单位] [nvarchar](50) NULL,
CONSTRAINT [PK_收费记录单] PRIMARY KEY CLUSTERED
(
[_Identify] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
存储过程代码： USE [编号测试]
GO
/****** 对象: StoredProcedure [dbo].[编号生成] 脚本日期: 10/14/2014 10:23:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[编号生成]
@cprefix nvarchar(255) = "AAAA141013-", --前缀
@ctablename nvarchar(255) = "费用记录单" --表名
--@BH int OUTPUT --新编号
AS
BEGIN
SET NOCOUNT ON
DECLARE @Key1 int,@Key2 int,@countid int
set @countid = dbo.countBH(@cprefix,@ctablename)
if @countid = 0
BEGIN
Insert Into [编号] (表名, 日期, 前缀, 顺序号, 已用标识) Values(@ctablename,getdate(),@cprefix,1,0)
END
set @Key1 = dbo.minBH(@cprefix,@ctablename)
set @Key2 = dbo.maxBH(@cprefix,@ctablename)
Update [编号] Set [已用标识] = 1 Where [顺序号] = @Key1 And [前缀] = @cprefix And 表名 = @ctablename
if @Key1 = @Key2
BEGIN
Insert Into [编号] (表名, 日期, 前缀, 顺序号, 已用标识) Values(@ctablename,getdate(),@cprefix,@Key2 + 1,0)
END
DELETE FROM [编号] Where 表名 = @ctablename and 前缀 = @cprefix and 顺序号 = @Key1 And 已用标识 = 1
return @Key1
END
三个自定义函数代码： USE [编号测试]
GO
/****** 对象: UserDefinedFunction [dbo].[countBH] 脚本日期: 10/14/2014 10:24:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--编号表是否存在前缀的行
create function [dbo].[countBH](@prefixbh varchar(255),@tablename varchar(255))
returns int
as
begin
declare @mycount int
select @mycount=Count(*) from [编号]
Where [前缀] = @prefixbh And 表名 = @tablename and 已用标识 = 0
return @mycount
end
USE [编号测试]
GO
/****** 对象: UserDefinedFunction [dbo].[maxBH] 脚本日期: 10/14/2014 10:24:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--编号表是否存在前缀的行
create function [dbo].[maxBH](@prefixbh varchar(255),@tablename varchar(255))
returns int
as
begin
declare @mymaxbh int
Select @mymaxbh=Max(顺序号) From [编号]
Where [前缀] = @prefixbh And 表名 = @tablename and 已用标识 = 0
return @mymaxbh
end
USE [编号测试]
GO
/****** 对象: UserDefinedFunction [dbo].[minBH] 脚本日期: 10/14/2014 10:25:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--编号表是否存在前缀的行
CREATE function [dbo].[minBH](@prefixbh varchar(255),@tablename varchar(255))
returns int
as
begin
declare @myminbh int
Select @myminbh=Min(顺序号) From [编号]
Where [前缀] = @prefixbh And 表名 = @tablename and 已用标识 = 0 Or 已用标识 Is Null
return @myminbh
end
以上为存储过程中用到的代码
下一楼 放源文件
|
|
此主题相关图片如下：11qq截图201322.png
