我现在改成这样乐:CREATE PROCEDURE sp_GenOrdinal(@name char(8),@value char(10) Output) AS
BEGIN TRANSACTIONselect @value=[value] from usrordinal UPDLOCK where [name]=@nameif @value is NULL /* if not found variable,create it */ begin
select @value=convert(char(6),getdate(),12)+'0001'
insert usrordinal([name],[value]) values(@name,@value)
endelse /* if found variable,update it's value */ begin
if substring(@value,1,6)=convert(char(6),getdate(),12)
select @value=convert(char(6),getdate(),12)+right('0000'+rtrim(convert(char(4),convert(int,right(@value,4))+1)),4)
else
select @value=convert(char(6),getdate(),12)+'0001'
UPDATE usrordinal SET [value] = @value WHERE [name]=@name
endCOMMIT TRANSACTION
GO
可是最后调用这个存储器:
Edit1->Text=SystemData->spGenOrdinal->Parameters->ParamByName("@value")->Value;
出来的结果是031003000,就是最后一位不出来,不知道错在哪里乐?
BEGIN TRANSACTIONselect @value=[value] from usrordinal UPDLOCK where [name]=@nameif @value is NULL /* if not found variable,create it */ begin
select @value=convert(char(6),getdate(),12)+'0001'
insert usrordinal([name],[value]) values(@name,@value)
endelse /* if found variable,update it's value */ begin
if substring(@value,1,6)=convert(char(6),getdate(),12)
select @value=convert(char(6),getdate(),12)+right('0000'+rtrim(convert(char(4),convert(int,right(@value,4))+1)),4)
else
select @value=convert(char(6),getdate(),12)+'0001'
UPDATE usrordinal SET [value] = @value WHERE [name]=@name
endCOMMIT TRANSACTION
GO
可是最后调用这个存储器:
Edit1->Text=SystemData->spGenOrdinal->Parameters->ParamByName("@value")->Value;
出来的结果是031003000,就是最后一位不出来,不知道错在哪里乐?
begin
set @value=convert(char(6),getdate(),12)+'0001'
insert usrordinal([name],[value]) values(@name,@vlaue)
end
else
begin
begin tran
select @value=convert(char(6),getdate(),12)+right(10001+right(value,4),4) from usrordinal where [name]=@name and value like convert(char(6),getdate(),12)+'%'
UPDATE usrordinal SET [value]=@value WHERE [name]=@name
commit tran
end
go你在查询分析器里调用执行:
delclare @a char(10) output
exec sp_GenOrdinal '12345678',@a output
select @a 结果
改为:if exists(select [value] from usrordinal where [name]=@name)
begin
select @value=[value] from usrordinal UPDLOCK where [name][email protected]
set @value = '0310040002' -- set @value = null
if @value is null
select @value = convert(char(6),getdate(),12)+'0001'
else
begin
if substring(@value,1,6)=convert(char(6),getdate(),12)
select @value=convert(char(6),getdate(),12)+right('0000'+rtrim(convert(char(4),convert(int,right(@value,4))+1)),4)
else
select @value=convert(char(6),getdate(),12)+'0001'
end
select @value
----------
0310040003(所影响的行数为 1 行)