Invalid length parameter passed to the SUBSTRING function.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Invalid length parameter passed to the...

Invalid length parameter passed to the SUBSTRING function.

Rate Topic

Display Mode

Topic Options

Author

Message

kjkeyan

Posted Wednesday, June 16, 2010 6:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 12:39 PM
Points: 9, Visits: 305

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

Post #938115

spaghettidba

Posted Wednesday, June 16, 2010 7:12 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 4,804, Visits: 8,068

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.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #938149

Eugene Elutin

Posted Wednesday, June 16, 2010 7:20 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:15 AM
Points: 2,538, Visits: 4,361

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!" BigGrin

(So many miracle inventions provided by MS to us...)

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

Post #938155

« Prev Topic | Next Topic »

Permissions