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
17
Views
9 Months
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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.