如何生成自动字母数字inv。 SQL存储过程中的数字 [英] How to generate auto alpha numeric inv. Number in SQL store procedure

问题描述

如何生成自动Alpha数字Inv。 SQL Store程序中的数字

i使用Sql Server 2008 r2和vb.net 2012

我收到错误----



Msg 245，Level 16，State 1，Procedure P_GetAutoIDPurchaseInv，第13行

转换varchar值'17 -18/00002时转换失败'到数据类型int。



当我刚用这行时它工作正常但是当我使用完整代码然后显示错误请帮助我先生 -

how to Generate Auto Alpha numeric Inv. number in SQL Store procedure
i used Sql Server 2008 r2 and vb.net 2012
I got a error ----

Msg 245, Level 16, State 1, Procedure P_GetAutoIDPurchaseInv, Line 13
Conversion failed when converting the varchar value '17-18/00002' to data type int.

When i just use in this lines then it work fine but when i use full code then show the error please help me sir--

<pre><pre>alter proc P_GetAutoIDPurchaseInv
as
--select top 1 purinvid from tbl_pur_inv order by purinvid desc

begin
if (select top 1 purinvid from tbl_pur_inv order by purinvid desc)=0
declare @lastval char(11) 
set @lastval = '17-18/00001' 
select @lastval 'purinvid'
end

我尝试过：

What I have tried:

alter proc P_GetAutoIDPurchaseInv
as
--select top 1 purinvid from tbl_pur_inv order by purinvid desc

begin
if (select top 1 purinvid from tbl_pur_inv order by purinvid desc)=0
declare @lastval char(11) 
set @lastval = '17-18/00001' 

declare @i int 
	set @i = right(@lastval,5) + 1 
	--return 'C' + right('000' + convert(varchar(10),@i),4) 
	return '17-18/'+ right('0000' + convert(varchar(11), @i),5) 
select @lastval 'purinvid'
end

推荐答案

- 你可以试试这个

--You can try this

--CREATE A TEMPORARY TABLE
CREATE TABLE #myTemp123
(
                Id INT NOT NULL IDENTITY(1,1),
                AutoId AS 'ABC' + RIGHT('0000'+ CONVERT(VARCHAR(5),Id),5)
)
--INSERT DEFAULT 10000 RECORDS AT ONCE IN LOOP Simply TYPE GO 10000
INSERT INTO #myTemp123 DEFAULT VALUES;
--CHECK INSERTED RECORD
SELECT * FROM #myTemp123

这篇关于如何生成自动字母数字inv。 SQL存储过程中的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！