Couldn't fix the problem of converting types from varchar to int by this request, I wrote
I need help to fix a type conversion issue that is bugging me for hours in sp that I wrote. I want to split the account number like this '12 / SH / IFCR / 7 'and get the last int value and store it separately
Eg. '12/SH/IFCR/7'
→ 12/SH/IFCR/
and 7
Note. 12/SH/IFCR/
- a prefix that remains unchanged, but the last number changes
ALTER PROCEDURE spGenerateCreditInvoiceForApi
@ShopId as int,
@TransId as int
--@CompanyId as int
AS
BEGIN
SET NOCOUNT ON;
declare @CompanyId as int
declare @Prefix as varchar(50)
declare @ProformaId as int
declare @MaxId as int
declare @FinId as int
declare @InvoiceNo as varchar(150)
set @CompanyId=(select CompanyID from aShops where ShopID=@ShopId)
set @FinId=(Select financialid from afinancialyear where Curfinancialyear = 1 and companyid = @CompanyId)
set @Prefix=(SELECT Prefix FROM aPrefix WHERE InterfaceID = 1504 and ShopId=@ShopId and FinancialId = @FinId)
set @ProformaId=(select ISNULL(MAX(CONVERT(INT,REVERSE(LEFT((REVERSE(ihInvoiceNo)),(PATINDEX('%/%' ,(REVERSE (ihInvoiceNo))))-1)))),0)
from LOsInvoiceHeader
where ihInvoiceID= @TransId and ihShopID=@ShopId)
--SET @intBillID = (SELECT CASE WHEN COUNT(poshBillid)=0 THEN 1 ELSE MAX(poshBillid)+1 END FROM losposheader WHERE poshShopID=@intShopId)
set @MaxId=(SELECT CASE WHEN COUNT(ihInvoiceNo)=0 THEN 1
ELSE MAX(ihInvoiceNo)+1 END
from losinvoiceheader
where ihShopId =@ShopId and ihfinancialid=@FinId and ihType='I')
SET @InvoiceNo = (@Prefix+CONVERT(VARCHAR,@MaxId))
--update LOsInvoiceHeader set ihInvoiceNo=@InvoiceNo, ihProformaID=@ProformaId where ihInvoiceID=@TransId and ihShopID=@ShopId
--print @InvoiceNo
END
GO
Mistake:
Msg 245, Level 16, State 1, Procedure spGenerateCreditInvoiceForApi, Line 33
Conversion failed while converting varchar '12 / SH / IFCR / 7 'to int.
Thanks in advance.
source to share
Thank goodness I found the problem
I changed the use of Max () to count () and added a transform method to the whole request
Before
set @MaxId=(SELECT CASE WHEN COUNT(ihInvoiceNo)=0 THEN 1
ELSE MAX(ihInvoiceNo)+1 END
from losinvoiceheader
where ihShopId =@ShopId and ihfinancialid=@FinId and ihType='I')
After
set @MaxId=CONVERT(INT,(SELECT CASE WHEN COUNT(ihInvoiceNo)=0 THEN 1
ELSE CONVERT(INT,COUNT(ihInvoiceNo)+1) END
from losinvoiceheader
where ihShopId =@ShopId and ihfinancialid=@FinId and ihType='I'))
source to share
How about something like this
declare @bill varchar(50),
@reversebill varchar(50),
@reverseResult varchar(50)
select @bill = '12/SH/IFCR/73'
select @reversebill = REVERSE(@bill)
select @reverseResult = SUBSTRING(@reversebill,0,CHARINDEX('/',@reversebill))
select Reverse(@reverseResult)
source to share