27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[Cust_info_test] (
[Cust_ID] varchar(20) NOT NULL ,
[Cust_Name] nvarchar(20) NULL ,
[Cust_Sex] int NULL DEFAULT ((0)) ,
[Cust_Birthday] datetime NULL ,
[Cust_Age] int NULL DEFAULT ((0)) ,
[Cust_FH] int NULL DEFAULT ((0)) ,
[Cust_Height] int NULL DEFAULT ((0)) ,
[Cust_Weight] float(53) NULL DEFAULT ((0)) ,
[Cust_Marry] varchar(3) NULL ,
[Create_Date] datetime NULL DEFAULT (getdate()) ,
[Cust_Servicer] varchar(8) NULL ,
[Tel1] varchar(20) NULL ,
[Tel2] varchar(20) NULL ,
[Tel3] varchar(20) NULL ,
[ID] int NOT NULL IDENTITY(1,1)
)
创建表
create procedure [dbo].[PROC_Insert_CustInfo_test]
@tel nvarchar(20),
@agent nvarchar(8),
@cust_id varchar(20) output
as
declare @id int
declare @new_id int
DECLARE @error_var int, @rowcount_var int
if exists (
select 1 from Cust_info where Tel1=@tel or Tel2=@tel or Tel3=@tel
)
select top 1 @cust_id=cust_id from cust_info where tel1=@tel or Tel2=@tel or Tel3=@tel
else if exists( select 1 from Cust_info where Tel1=substring(@tel,2,len(@tel))
or Tel2=substring(@tel,2,len(@tel))
or Tel2=substring(@tel,2,len(@tel)) )
select top 1 @cust_id=cust_id from cust_info where Tel1=substring(@tel,2,len(@tel))
or Tel2=substring(@tel,2,len(@tel))
or Tel2=substring(@tel,2,len(@tel))
else
begin
------------------开始插入客户信息事务-------------------
SELECT @new_id = IDENT_CURRENT('Cust_info_test')+1
INSERT INTO dbo.Cust_info_test (Cust_ID,tel1,Cust_Servicer) VALUES(@new_id,@tel,@agent)
select @id=COUNT(cust_id)
from Cust_info_test
where convert(date,Create_Date,120)=convert(date,GETDATE(),20)
set @id+=1
set @cust_id = dbo.FUN_Make_ID('B',@id,5)
UPDATE Cust_info_test SET cust_id=@cust_id WHERE ID=@new_id
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
begin tran
WHILE (@rowcount_var<1)
BEGIN
select @id=COUNT(cust_id)
from Cust_info_test
where convert(date,Create_Date,120)=convert(date,GETDATE(),20)
set @id+=1
set @cust_id = dbo.FUN_Make_ID('B',@id,5)
UPDATE Cust_info_test SET cust_id=@cust_id WHERE ID=@new_id
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
END
commit tran
end
存储过程
create function [dbo].[FUN_Make_ID]
(
@Lt_mark varchar(10),
@Id_seed int,
@len int
)
returns varchar(30)
as
begin
declare @ID_str varchar(20)
declare @date varchar(10)
set @date=replace (substring(convert( varchar(10),getdate(),120),3,8),'-','');
set @ID_str=CAST(@Id_seed as varchar(20))
if @lt_mark is null
begin
while LEN(@ID_str) < @len
begin
set @ID_str='0'+@ID_str;
end
set @ID_str=@date+@ID_str
end
else
begin
while LEN(@ID_str) < @len
begin
set @ID_str='0'+@ID_str;
end
set @ID_str=@Lt_mark+@date+@ID_str
end
return @id_str;
end
-- 第二个:
while LEN(@ID_str) < @len
begin
set @ID_str='0'+@ID_str;
end
-- 这个while 改成一个 语句比较好
set @ID_str = REPLICATE('0',@len -len(@ID_str)) + @ID_str