如何在插入前增加varchar值的一部分 [英] How to increment a part of varchar value before insert

问题描述

大家好，



我必须在sql预插入存储过程中增加字段的varchar值的一部分。值的第一部分将是年和月，剩余部分将是0001,0002 .....必须在存储过程中递增。将值0001转换为int或numberic后，它将变为1位数。有人可以帮帮我吗？



我尝试了什么：

Hi All,

I have to increment a part of varchar value of field in sql pre insert stored procedure. The first portion of the value will be year and month and remaining portion will be 0001, 0002..... which has to increment in stored procedure. After converting value 0001 into int or numberic it's getting as 1 digit. Can anyone help me please?

What I have tried:

Alter procedure [dbo].[taPMTransactionInsertPre]  @I_vBACHNUMB char(15) output,         @I_vVCHNUMWK char(17) output,            @I_vVENDORID char(15) output,            @I_vDOCNUMBR char(20) output,            @I_vDOCTYPE  smallint output,            @I_vDOCAMNT  numeric(19,5) output,     @I_vDOCDATE  datetime output,    @I_vPSTGDATE datetime output,         @I_vVADCDTRO char(15) output,   @I_vVADDCDPR char(15) output,         @I_vPYMTRMID char(20) output,     @I_vTAXSCHID char(15) output,         @I_vDUEDATE  datetime output,         @I_vDSCDLRAM numeric(19,5) output,     @I_vDISCDATE datetime output,         @I_vPRCHAMNT numeric(19,5) output,     @I_vCHRGAMNT numeric(19,5) output,     @I_vCASHAMNT numeric(19,5) output,    @I_vCAMCBKID char(15) output,         @I_vCDOCNMBR char(20) output,         @I_vCAMTDATE datetime output,         @I_vCAMPMTNM char(20) output,         @I_vCHEKAMNT numeric(19,5) output,     @I_vCHAMCBID char(15) output,         @I_vCHEKDATE datetime output,         @I_vCAMPYNBR char(20) output,         @I_vCRCRDAMT numeric(19,5) output,     @I_vCCAMPYNM char(20) output,         @I_vCHEKNMBR char(20) output,         @I_vCARDNAME char(15) output,         @I_vCCRCTNUM char(20) output,         @I_vCRCARDDT datetime output,         @I_vCHEKBKID char(15) output,         @I_vTRXDSCRN char(30) output,         @I_vTRDISAMT numeric(19,5) output,     @I_vTAXAMNT numeric(19,5) output,      @I_vFRTAMNT numeric(19,5) output,      @I_vTEN99AMNT numeric(19,5) output,    @I_vMSCCHAMT numeric(19,5) output,     @I_vPORDNMBR char(20) output,         @I_vSHIPMTHD char(15) output,         @I_vDISAMTAV numeric(19,5) output,     @I_vDISTKNAM numeric(19,5) output,     @I_vAPDSTKAM numeric(19,5) output,     @I_vMDFUSRID char(15) output,         @I_vPOSTEDDT datetime output,         @I_vPTDUSRID char(15) output,         @I_vPCHSCHID char(15) output,         @I_vFRTSCHID char(15) output,         @I_vMSCSCHID char(15) output,         @I_vPRCTDISC numeric(19,2) output,  @I_vTax_Date datetime output,       @I_vCURNCYID char(15) output,   @I_vXCHGRATE numeric(19,7) output,  @I_vRATETPID char(15) output,   @I_vEXPNDATE datetime output,   @I_vEXCHDATE datetime output,   @I_vEXGTBDSC char(30) output,   @I_vEXTBLSRC char(50) output,   @I_vRATEEXPR smallint output,     @I_vDYSTINCR smallint output,   @I_vRATEVARC numeric(19,7) output,  @I_vTRXDTDEF smallint output,   @I_vRTCLCMTD smallint output,   @I_vPRVDSLMT smallint output,   @I_vDATELMTS smallint output,   @I_vTIME1 datetime output,   @I_vBatchCHEKBKID char(15) output,  @I_vCREATEDIST smallint output,   @I_vRequesterTrx smallint output,  @I_vUSRDEFND1 char(50) output,   @I_vUSRDEFND2 char(50) output,   @I_vUSRDEFND3 char(50) output,   @I_vUSRDEFND4 varchar(8000) output,  @I_vUSRDEFND5 varchar(8000) output,  @O_iErrorState int output,  @oErrString varchar(255) output
as

declare
@VendorID char(30),
@VDOCNMBR char(30),
@szResult char(30),
@SQL	  varchar(4000)

Set @SQL = ''
set nocount on
set @oErrString = ''
set @O_iErrorState = 0

Begin

Declare C1 Cursor For
		select distinct max(a.DOCNUMBR)
		from dbo.PM10000 a
		where a.VENDORID = @VendorID
		and a.DOCNUMBR like ltrim(rtrim(@VDOCNMBR)) + '%'
		union
		select distinct max(a.DOCNUMBR)
		from dbo.PM20000 a
		where a.VENDORID = @VendorID
		and a.DOCNUMBR like ltrim(rtrim(@VDOCNMBR)) + '%'
		union
		select distinct max(a.DOCNUMBR)
		from dbo.PM30200 a
		where a.VENDORID = @VendorID
		and a.DOCNUMBR like ltrim(rtrim(@VDOCNMBR)) + '%'

	Open C1
		Fetch Next From C1 Into @szResult
		While @@Fetch_Status=0 
 
 begin
	

			If @szResult is not Null 
				Begin
			
				set @szResult = SUBSTRING(@szResult,8,4)
	
				set @szResult =cast ((abs(cast ( @szResult as integer)+1)) as char)

				--print @szResult
		
				Set @szResult ='000'+@szResult+1

				set @I_vDOCNUMBR =@VDOCNMBR+'-'+@szResult
				End
			Else
				Begin
				Set @szResult =@VDOCNMBR+'-0001'
				set @I_vDOCNUMBR =@szResult
				End
	
		  Fetch Next From C1 Into @szResult
			
		
	    end
		Close C1
		Deallocate C1

		
set nocount off
end
return 0

GO

推荐答案

不要。

这只会导致眼泪...

更好的解决方案是使用计算列：在表中指定计算列 [ ^ ]与身份字段相结合将产生您想要的结果，而不会出现可能的问题您将会见。

Don't.
It'll only lead to tears...
A better solution is to use a Computed column: Specify Computed Columns in a Table[^] which combined with an identity field will produce the result you want without the possible problems you will meet.

如果我正确解释您的代码，此代码段是相关部分。

If I interpret your code correctly, this code snippet is the relevant part.

If @szResult is not Null 
Begin

    set @szResult = SUBSTRING(@szResult, 8, 4)

    set @szResult = cast ((abs(cast(@szResult as integer) + 1)) as char)

    --print @szResult

    Set @szResult ='000' + @szResult + 1

    set @I_vDOCNUMBR = @VDOCNMBR + '-' + @szResult
End
Else
Begin
    Set @szResult = @VDOCNMBR + '-0001'
    set @I_vDOCNUMBR = @szResult
End

对我来说，看起来你将值增加两次，一次在这里

To me it looks like you increment the value twice, once here

set @szResult = cast ((abs(cast(@szResult as integer) + 1)) as char)

然后再来这里

and then again here

Set @szResult ='000' + @szResult + 1

你也工作字符串而不是整数。

最好声明一个临时变量来执行操作。

Also you work in a string instead of an integer.
Might be better to declare a temporary variable to do the operations on.

declare @counter integer;
set @szResult = SUBSTRING(@szResult, 8, 4);
set @counter = cast(@szResult as integer);
set @counter = @counter + 1;
set @szResult = FORMAT(@counter, 'D4'); -- fills up with zeroes, so 1 -> 0001
... -- The rest of your code

要将数值格式化为字符串，请参阅 FORMAT（Transact-SQL） ） [ ^ ] < br $> b $ b和

标准数字格式字符串 [ ^ ]

这篇关于如何在插入前增加varchar值的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！