0

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-

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
2
Contributors
1
Reply
15
Views
3 Weeks
Discussion Span
Last Post by hericles
0

Going by the error I would assume the stored prodcedure is expecting to return an integer but you're returning a varchar. Would that be correct?
If yes, you just need to change the output type to be varchar.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.