Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invalid length parameter passed to the SUBSTRING function.


Invalid length parameter passed to the SUBSTRING function.

Author
Message
kjkeyan
kjkeyan
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 307
Hi All,
The Below sp throwing error that i mention in the step1 and its working fine when i am executing the step2 i.e when i pass the @RangeString parameter to 100 count its giving error
but if i pass 99 count its working fine.
please provide me some solution for this issue
--/*Step1*/
--/*Error Msg Msg 536, Level 16, State 5, Procedure TRA_BrokenSalesPROCV2, Line 194
--Invalid length parameter passed to the SUBSTRING function*/
--EXEC TRA_BrokenSalesPROCV6 500003,'50000310261166638','1234567890123456','141-99~1,98~1,97~1,96~1,95~1,94~1,93~1,92~1,91~1,90~1,89~1,88~1,87~1,86~1,85~1,84~1,83~1,82~1,81~1,80~1,79~1,78~1,77~1,76~1,75~1,74~1,73~1,72~1,71~1,70~1,69~1,68~1,67~1,66~1,65~1,64~1,63~1,62~1,61~1,60~1,59~1,58~1,57~1,56~1,55~1,54~1,53~1,52~1,51~1,50~1,49~1,48~1,47~1,46~1,45~1,44~1,43~1,42~1,41~1,40~1,39~1,38~1,37~1,36~1,35~1,34~1,33~1,32~1,31~1,30~1,29~1,28~1,27~1,26~1,25~1,24~1,23~1,22~1,21~1,20~1,19~1,18~1,17~1,16~1,15~1,14~1,13~1,12~1,11~1,10~1,09~1,08~1,07~1,06~1,05~1,04~1,03~1,02~1,01~1,00~1,|',64800,'141-99~1,98~1,97~1,96~1,95~1,94~1,93~1,92~1,91~1,90~1,89~1,88~1,87~1,86~1,85~1,84~1,83~1,82~1,81~1,80~1,79~1,78~1,77~1,76~1,75~1,74~1,73~1,72~1,71~1,70~1,69~1,68~1,67~1,66~1,65~1,64~1,63~1,62~1,61~1,60~1,59~1,58~1,57~1,56~1,55~1,54~1,53~1,52~1,51~1,50~1,49~1,48~1,47~1,46~1,45~1,44~1,43~1,42~1,41~1,40~1,39~1,38~1,37~1,36~1,35~1,34~1,33~1,32~1,31~1,30~1,29~1,28~1,27~1,26~1,25~1,24~1,23~1,22~1,21~1,20~1,19~1,18~1,17~1,16~1,15~1,14~1,13~1,12~1,11~1,10~1,09~1,08~1,07~1,06~1,05~1,04~1,03~1,02~1,01~1,00~1,|'
--/*Step 2*/
--/*Working Fine*/
--EXEC TRA_BrokenSalesPROCV6 500003,'50000310261166638','1234567890123456','141-98~1,97~1,96~1,95~1,94~1,93~1,92~1,91~1,90~1,89~1,88~1,87~1,86~1,85~1,84~1,83~1,82~1,81~1,80~1,79~1,78~1,77~1,76~1,75~1,74~1,73~1,72~1,71~1,70~1,69~1,68~1,67~1,66~1,65~1,64~1,63~1,62~1,61~1,60~1,59~1,58~1,57~1,56~1,55~1,54~1,53~1,52~1,51~1,50~1,49~1,48~1,47~1,46~1,45~1,44~1,43~1,42~1,41~1,40~1,39~1,38~1,37~1,36~1,35~1,34~1,33~1,32~1,31~1,30~1,29~1,28~1,27~1,26~1,25~1,24~1,23~1,22~1,21~1,20~1,19~1,18~1,17~1,16~1,15~1,14~1,13~1,12~1,11~1,10~1,09~1,08~1,07~1,06~1,05~1,04~1,03~1,02~1,01~1,00~1,|',64800,'141-98~1,97~1,96~1,95~1,94~1,93~1,92~1,91~1,90~1,89~1,88~1,87~1,86~1,85~1,84~1,83~1,82~1,81~1,80~1,79~1,78~1,77~1,76~1,75~1,74~1,73~1,72~1,71~1,70~1,69~1,68~1,67~1,66~1,65~1,64~1,63~1,62~1,61~1,60~1,59~1,58~1,57~1,56~1,55~1,54~1,53~1,52~1,51~1,50~1,49~1,48~1,47~1,46~1,45~1,44~1,43~1,42~1,41~1,40~1,39~1,38~1,37~1,36~1,35~1,34~1,33~1,32~1,31~1,30~1,29~1,28~1,27~1,26~1,25~1,24~1,23~1,22~1,21~1,20~1,19~1,18~1,17~1,16~1,15~1,14~1,13~1,12~1,11~1,10~1,09~1,08~1,07~1,06~1,05~1,04~1,03~1,02~1,01~1,00~1,|'


ALTER PROC [dbo].[TRA_BrokenSalesPROCV6]
   @OutLetID INT,
   @BTransactionID VARCHAR(16),
   @UserRefId VARCHAR(20),
   @RangeString VARCHAR(8000),
   @InGameTime INT=9000,
   @GameRefIDs VARCHAR(1000)=NULL
AS

-- 0 Quota Not Available
-- 1 Success
-- 2 Stock Not Available
-- > 2 Returned from Terminal Status
-- -1 Draw Closed for the Day
-- -2 Draw Cancelled (SaleStatus in DailyProcessStatus = 2)
-- -3 Error In Purchase Input (Ranges Not Inputed)
-- -5 General Error


DECLARE @RangeID TINYINT; DECLARE @NoofTkts INT; DECLARE @LotRefID INT; DECLARE @ProviderID INT
DECLARE @RngStrLEN INT; DECLARE @RowValue VARCHAR(500); DECLARE @Pos INT;
DECLARE @RangeValue VARCHAR(500); DECLARE @RowValueLength INT; DECLARE @GameRefIDPos INT;
DECLARE @RangeValueLength INT; DECLARE @RangeTicketValue VARCHAR(20); DECLARE @GameID BIGINT;
DECLARE @RangeTicketValueLENgth INT; DECLARE @RecordIdValue VARCHAR(10); DECLARE @TotTktsUPDCDR TINYINT;
DECLARE @GameRefID INT; DECLARE @JDay SMALLINT; DECLARE @GameTimeID TINYINT; DECLARE @GameTimeInSec INT
DECLARE @GameDate SMALLDATETIME; DECLARE @GameNo SMALLINT; DECLARE @GameGroupID SMALLINT;
DECLARE @GameDay SMALLINT; DECLARE @SaleStatus INT; DECLARE @RC INT; DECLARE @BSaleStatus INT;
DECLARE @RatePerTicket DECIMAL(20,2); DECLARE @StkRatePerTicket DECIMAL(20,2); DECLARE @PurRatePerTicket DECIMAL(10,3);
DECLARE @ReqCnt INT; DECLARE @ReqCnt1 SMALLINT; DECLARE @BalQuota MONEY; DECLARE @RCnt INT;
DECLARE @ErrVal INT; DECLARE @AppVer VARCHAR(20); DECLARE @TerSts TINYINT;
DECLARE @DiffInTime INT; DECLARE @LTwoTranID VARCHAR(3); DECLARE @CurrTime INT;
DECLARE @CurStock SMALLINT; DECLARE @FTicketNoToAllot INT; DECLARE @LTicketNoToAllot INT;
DECLARE @NextTicketNoToAllot INT; DECLARE @RIDPad CHAR(4); DECLARE @MaxDiff SMALLINT;
DECLARE @TransactionID VARCHAR(20); DECLARE @CurDateTime DATETIME; DECLARE @HrID SMALLINT;
DECLARE @TotAmountPerRange DECIMAL(20,2);
DECLARE @TotVatAmount DECIMAL(10,2)
DECLARE @TotTransTickets INT; DECLARE @TotTransAmount DECIMAL(20,2); DECLARE @SOList VARCHAR(500);
DECLARE @TotReqTickets INT; DECLARE @TotReqAmount DECIMAL(15,2); DECLARE @StockOverListOP VARCHAR(500);
DECLARE @ReqtdLotIDs VARCHAR(1000); DECLARE @TReqTkts INT; DECLARE @Info VARBINARY(1000)
DECLARE @TReqAmt MONEY; DECLARE @PrvGameRefID INT; DECLARE @PrvGameTimeID INT;
DECLARE @TotSTKReqAmount MONEY; DECLARE @TotPURReqAmount MONEY;
DECLARE @TotSTKTransAmount MONEY; DECLARE @TotPurTransAmount MONEY;
DECLARE @TotSTKAmountPerRange FLOAT; DECLARE @TotPURAmountPerRange FLOAT;
DECLARE @LTransAmt MONEY; DECLARE @LTransSTKAmt MONEY; DECLARE @LTransPURAmt MONEY
DECLARE @ConAmount MONEY DECLARE @ConPWTAmt INT DECLARE @CFCrLimit INT; DECLARE @CrLimit INT
DECLARE @TempCrLimit INT DECLARE @ConDispAmt INT DECLARE @Diff MONEY DECLARE @Vat MONEY
DECLARE @GameTypeID INT
DECLARE @TransCounter INT;
DECLARE @MainDetails VARCHAR(8000); DECLARE @TicketsDetails VARCHAR(8000); DECLARE @ReqDetails VARCHAR(8000);
SET @ReqDetails=''
SET @MainDetails=''
SET @TicketsDetails=''



SET NOCOUNT ON

SET @GameRefIDPos=CHARINDEX('-',@RangeString,0)
SET @GameRefID =LEFT(@RangeString,@GameRefIDPos-1)
SET @JDay =DATEPART(dy,GETDATE())
SET @RIDPad ='0000'
SET @LTwoTranID='00'
SET @ReqCnt=1
SET @ReqCnt1=1
SET @TransactionID=@BTransactionID+@LTwoTranID
PRINT @TransactionID




SET @MaxDiff=120
BEGIN TRANSACTION
SELECT @TransCounter=ISNULL(TransCounter,0)+1
FROM OutLetSales
WHERE OutLetID=@OutLetID AND GameJulianDay = @JDay


SET @CurrTime=DATEPART(HH,GETDATE())*3600+DATEPART(MI,GETDATE())*60+DATEPART(SS,GETDATE())
SET @DiffInTime=(@InGameTime-@CurrTime)

SET @TotReqTickets=0
SET @TotReqAmount=0
SET @TotSTKReqAmount=0
SET @TotPURReqAmount=0
SET @TReqTkts=0
SET @TReqAmt=0
SET @ReqtdLotIDs=''
SET @PrvGameRefID=''
SET @PrvGameTimeID=''



IF (@GameDay=@JDay)
BEGIN
IF (@CurrTime >(@InGameTime-30)) -- Game Closed for the Day
BEGIN
PRINT @CurrTime
PRINT @InGameTime-30

SELECT -1 TS
SELECT CONVERT(VARCHAR(15),@InGameTime)+'#'+'-1000' EC
ROLLBACK TRAN
RETURN
END
END

SET @RngStrLEN = LEN(@RangeString)
WHILE(@RngStrLEN > 0)
BEGIN
SET @CurDateTime=GETDATE()
SET @HrID=DATEPART(HH,@CurDateTime)
SET @TotTransTickets=0
SET @TotTransAmount=0

SET @TotSTKTransAmount=0
SET @TotPurTransAmount=0

SET @Pos = CHARINDEX('|', @RangeString)
SET @RowValue = LEFT(@RangeString, @Pos - 1)
SET @RangeString = RIGHT(@RangeString, @RngStrLEN - @Pos )
SET @RowValueLENgth = LEN(@RowValue)
SET @Pos = CHARINDEX('-', @RowValue)
SET @LotRefID=LEFT(@RowValue, @Pos - 1)
SET @RangeValue = RIGHT(@RowValue, @RowValueLENgth - @Pos )
SET @RangeValueLENgth = LEN(@RangeValue)

SET @RecordIdValue=''
WHILE(@RangeValuelength > 0)
BEGIN

SET @RecordIdValue=@LotRefID
SET @Pos = CHARINDEX(',', @RangeValue)
SET @RangeTicketValue=LEFT(@RangeValue, @Pos - 1)
SET @RangeValue = RIGHT(@RowValue, @RangeValueLENgth - @Pos )
SET @RangeValueLENgth = LEN(@RangeValue)
SET @Pos = CHARINDEX('~',@RangeTicketValue)
SET @RangeTicketValueLENgth = LEN(@RangeTicketValue)
SET @RangeID = LEFT(@RangeTicketValue, @Pos - 1)
SET @NoOfTkts = RIGHT(@RangeTicketValue, @RangeTicketValueLENgth - @Pos)

IF @RecordIdValue<>@PrvGameRefID
BEGIN

SELECT @GameDate=GameDate,@GameDay=GameJulianDay,@GameNo=GameNo,@GameGroupID=MGameGroupID,@RatePerTicket=Rate,@GameTimeID=GameTimeID,
@StkRatePerTicket=CONVERT(DECIMAL(20,2),RetailerRate),
@PurRatePerTicket=CONVERT(DECIMAL(20,3),OutLetRate), @Vat = Vat,
@GameTypeID=GameTypeID
FROM [TestJetLottBusinessDB].[dbo].[TicketsAlloted] WHERE GameRefID=@RecordIdValue


IF @@ROWCOUNT=0
BEGIN
ROLLBACK TRANSACTION

SELECT -3 TS
SELECT '-1001' EC
RETURN
END

PRINT @StkRatePerTicket
PRINT @PurRatePerTicket

END

SELECT @SaleStatus=SaleStatus,@BSaleStatus=BSaleStatus,@GameTimeInSec = GameTimeInSec
FROM TestJetLottBusinessDB.[dbo].[TicketsAlloted] WHERE GameRefID=@RecordIdValue
SElect @CurrTime=DATEPART(HH,GETDATE())*3600+DATEPART(MI,GETDATE())*60+DATEPART(SS,GETDATE())

IF (@SaleStatus=2) Or (@CurrTime >= (@Gametimeinsec - 30))


BEGIN
SELECT -1 TS
SELECT '-1000' EC
ROLLBACK TRAN
RETURN
END

SET @TotAmountPerRange=(@NoOfTkts*@RatePerTicket)
SET @TotSTKAmountPerRange=(@NoOfTkts*@StkRatePerTicket)
SET @TotPURAmountPerRange=(@NoOfTkts*@PurRatePerTicket)

PRINT 'STKAmountPerRange'
PRINT @TotSTKAmountPerRange
PRINT 'PURAmountPerRange'
PRINT @TotPURAmountPerRange

IF @GameTypeID=1
BEGIN

SET @GameID =CONVERT(VARCHAR(3),@GameDay)+RIGHT(@RIDPad+@RecordIdValue,5)+CONVERT(VARCHAR(2),@RangeID)
PRINT @GameID




SELECT @FTicketNoToAllot=TicketNoToAllot,@CurStock=CurrentStock FROM CurrentGameBrokenNos WITH (UPDLOCK)
WHERE GameID=@GameID

IF @@ROWCOUNT=0
BEGIN
ROLLBACK TRANSACTION
-- Game Not in CurrentGameBrokenNos
SELECT -3 TS
SELECT '-1001' EC
RETURN
END

IF (@CurStock=0) OR (@CurStock < @NoOfTkts)
BEGIN
ROLLBACK TRANSACTION

EXEC @RC=TRA_BrokenRangeStockOverList @GameRefIDs,@StockOverListOP=@SOList OUTPUT
IF @RC<>0
BEGIN
SELECT -5 TS
SELECT '-1005' EC
ROLLBACK TRANSACTION
RETURN
END

SELECT 2 TS
SELECT @SOList AS SO
RETURN
END

SET @LTicketNoToAllot=@FTicketNoToAllot+((@NoOfTkts-1)*100)
SET @NextTicketNoToAllot=@LTicketNoToAllot+100
SET @TotTktsUPDCDR=@NoOfTkts
SET @NoOfTkts=@NoOfTkts

   END

IF @GameTypeID=2
BEGIN
DECLARE @Seed    INT
DECLARE @Min    INT
DECLARE @Max    INT
DECLARE @Number   INT

SET @Min   =1000
SET @Max   =9999

SELECT @Seed = CONVERT(INT, CONVERT(VARBINARY, REPLACE(CONVERT(VARCHAR(50), NEWID()), '-', '')))
SELECT @Number=@Min+ CAST(RAND(@Seed) *(@Max-@Min) AS INT)

SET @FTicketNoToAllot=CONVERT(VARCHAR(4),@Number)+RIGHT('00'+CONVERT(VARCHAR(2),@RangeID),2)
SET @LTicketNoToAllot=@FTicketNoToAllot+(((@NoOfTkts-1)*100))
END
IF @LTicketNoToAllot >999999 AND LEN(@LTicketNoToAllot) > 6
BEGIN
   ROLLBACK TRAN
   SELECT -5 TS
   SELECT 'SD-3000' EC
   RETURN
END

INSERT INTO TestJetLottBusinessDB.dbo.SalesDetails (SDTransactionID,RangeReq,GameRefID,MGameGroupID,RangeID,TicketFromNo,TicketToNo,GameTimeID,TotTickets,TotAmount,GameTypeID)
VALUES(@TransactionID,@ReqCnt,@RecordIdValue,@GameGroupID,@RangeID,@FTicketNoToAllot,@LTicketNoToAllot,@GameTimeID,@NoOfTkts,@TotAmountPerRange,@GameTypeID)

IF @@ERROR <> 0 OR @@Rowcount = 0
BEGIN
ROLLBACK TRAN
SELECT -5 TS
SELECT 'SD-3000' EC
RETURN
END
-- UPDATE CurrentGameBrokenNos From Here

IF @GameTypeID=1
BEGIN
UPDATE CurrentGameBrokenNos
SET TicketNoToAllot=@NextTicketNoToAllot,CurrentStock=CurrentStock-@TotTktsUPDCDR
WHERE GameID=@GameID
IF @@ERROR<>0 OR @@ROWCOUNT=0
BEGIN
ROLLBACK TRANSACTION
SELECT -5 TS
SELECT 'CDR-3000' EC
RETURN
END
-- UPDATE CurrentGameBrokenNos Till Here
END
SET @RngStrLEN = LEN(@RangeString)
SET @TotTransTickets=@TotTransTickets+@NoOfTkts
SET @TotTransAmount=@TotTransAmount+@TotAmountPerRange
SET @TotSTKTransAmount=@TotSTKTransAmount+@TotSTKAmountPerRange
SET @TotPurTransAmount=@TotPurTransAmount+@TotPURAmountPerRange
SET @ReqCnt=@ReqCnt+1
PRINT '@TotSTKTransAmount'
PRINT CONVERT(VARCHAR,@TotSTKTransAmount)
PRINT '@TotPurTransAmount'
PRINT CONVERT(VARCHAR,@TotPurTransAmount)



IF(@RecordIdValue<>@PrvGameRefID and @PrvGameRefID<>'')
BEGIN
SET @ReqtdLotIDs=@ReqtdLotIDS+CONVERT(VARCHAR,@PrvGameRefID)+'-'+CONVERT(VARCHAR,@TReqTkts)+'~'+CONVERT(VARCHAR,@TReqAmt)+'~'+CONVERT(VARCHAR,@PrvGameTimeID)+','+'|'
SET @TReqTkts=0
SET @TReqAmt=0
END
SET @TReqTkts=@TReqTkts+@NoOfTkts
SET @TReqAmt=@TReqAmt+@TotAmountPerRange
SET @TotVatAmount = (@TReqAmt * @Vat)/100
SET @PrvGameRefID=@RecordIdValue
SET @PrvGameTimeID=@GameTimeID

SET @TicketsDetails=@TicketsDetails+CONVERT(VARCHAR(10),@FTicketNoToAllot)+','+CONVERT(VARCHAR(10),@LTicketNoToAllot)+'|'

END

INSERT INTO [TestJetLottBusinessDB].[dbo].[SalesMaster](SMTransactionID,TransCounter,SReq,OutLetID,GameRefID,JulianDay,GameJulianDay,GameNo,HrID,TransDateTime,GameTimeID,TransType,TotTickets,TotalAmount,VatAmount,Status)
VALUES (@TransactionID,@TransCounter, @ReqCnt1,@OutLetID,@RecordIdValue,@JDay,@GameDay,@GameNo,@HrID,@CurDateTime,@GameTimeID,0,@TotTransTickets,@TotTransAmount,@TotVatAmount,1)

IF @@ERROR <> 0 OR @@Rowcount = 0
BEGIN
ROLLBACK TRAN
SELECT -5 TS
SELECT 'SM-3001' EC
RETURN
END


SET @MainDetails=CONVERT(VARCHAR(5),@RecordIdValue)+','+@TransactionID+','+ CONVERT(VARCHAR(10),@TotTransTickets)+','+CONVERT(VARCHAR(10),CONVERT(MONEY,@TotTransAmount)/100)+ ','+ CONVERT(VARCHAR(10),CAST(@Vat AS INT))+'%,' + CONVERT(VARCHAR(10),CONVERT(MONEY,@TotVatAmount)/100)+'*'+@TicketsDetails

PRINT @MainDetails
SET @ReqDetails=@ReqDetails+'-'+@MainDetails
SET @MainDetails=''
SET @TicketsDetails=''
SET @ReqCnt1=@ReqCnt1+1
SET @ReqCnt=1
SET @LTwoTranID=CAST(@LTwoTranID AS TINYINT)+1
SET @TransactionID=@BTransactionID+RIGHT('00'+CONVERT(VARCHAR(2),@LTwoTranID),2)

SET @TotReqTickets=@TotReqTickets+@TotTransTickets

SET @TotReqAmount =@TotReqAmount+@TotTransAmount
SET @TotSTKReqAmount=@TotSTKReqAmount+@TotSTKTransAmount
SET @TotPURReqAmount=@TotPURReqAmount+@TotPurTransAmount

PRINT '@TotSTKReqAmount'
PRINT CONVERT(VARCHAR,@TotSTKReqAmount)
PRINT '@TotPURReqAmount'
PRINT CONVERT(VARCHAR,@TotPURReqAmount)

END


SET @Info=CONVERT(VARBINARY(1000),@ReqtdLotIDs+CONVERT(VARCHAR,@PrvGameRefID)+'-'+CONVERT(VARCHAR,@TReqTkts)+'~'+CONVERT(VARCHAR,@TReqAmt)+'~'+CONVERT(VARCHAR,@GameTimeID)+','+'|')



SET @LTwoTranID=CAST(@LTwoTranID AS TINYINT)-1
SET @TransactionID=@BTransactionID+RIGHT('00'+CONVERT(VARCHAR(2),@LTwoTranID),2)


UPDATE TestJetLottBusinessDB.dbo.OutLetSales SET Info=@Info,
TransactionID=@UserRefId,BTrasID=@BTransactionID, TransCounter=@TransCounter,
LTransTkts=@TotReqTickets,LTransAmt=@TotReqAmount,
LTransSTKAmt=@TotSTKReqAmount,LTransPURAmt=@TotPURReqAmount,
CuSDayCuDrDayConTkts=CuSDayCuDrDayConTkts+@TotReqTickets,
CuSDayCuDrDayConAmt=CuSDayCuDrDayConAmt+CONVERT(MONEY,@TotReqAmount)/100,
CuSDayCudrDayVatConAmt = ISNULL(CuSDayCudrDayVatConAmt,0) + @TotVatAmount,
CuSDayCudrDayRetailerComAmt=CuSDayCudrDayRetailerComAmt+CONVERT(MONEY,@TotSTKReqAmount)/100,
CuSDayCudrDayOutLetConAmt=CuSDayCudrDayOutLetConAmt+CONVERT(MONEY,@TotPURReqAmount)/100,
CFCrLimit=CFCrLimit+CONVERT(MONEY,@TotReqAmount)/100,CStatus = 0 ,LTransTime = GETDATE(),LTransType = 1
WHERE OutLetID=@OutLetID AND GameJulianday=@Jday



SELECT @ErrVal=@@ERROR,@RCnt=@@ROWCOUNT

IF (@ErrVal<> 0) OR (@RCnt=0)

BEGIN
PRINT 'Here'
ROLLBACK TRANSACTION
SELECT -5 TS
SELECT '-2003' EC
RETURN
END



COMMIT TRAN


SELECT @CrLimit=CreditLimit ,@CFCrLimit=CFCrLimit,
@LTransAmt=LTransAmt,@ConAmount=CuSDayCuDrDayConAmt,
@CFCrLimit=CFCrLimit,
@TempCrLimit=TempCreditLimit,
@CrLimit=CreditLimit
FROM [TestJetLottBusinessDB].[dbo].[OutLetSales]
WHERE OutLetID=@OutLetID

SET @Diff=(@ConAmount-@ConPWTAmt)
SET @BalQuota=((@CrLimit/100.00)-@CFCrLimit)

PRINT '@ReqDetails'
PRINT @ReqDetails
PRINT '@BalQuota'
PRINT CONVERT(VARCHAR(100),@BalQuota)


SELECT 1 TS
SELECT @ReqDetails+'#'+CONVERT(VARCHAR(100),ISNULL(@BalQuota,0))+'#'+LEFT(CONVERT(VARCHAR,GETDATE()),11) + ' ' + CONVERT(VARCHAR,GETDATE(),108) TD


Thanks and Regards
kjkeyan
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 13305
You're not expecting somebody to look at all this code, are you?
Try to reduce the surface, or nobody will reasonably help you.
Take a look at the article linked in my signature line and find out a better way to post your questions.

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
Your error clearly states that it did happen in TRA_BrokenSalesPROCV2
How you think providing the TRA_BrokenSalesPROCV6 noodles will help to identify the problem, it does not use the SUBSTRING function itself?
Please provide text of TRA_BrokenSalesPROCV2.
This error is due to the negative number is passed as third (length) parameter to this function.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search