CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
hmahdavi
hmahdavi

Reputation: 2362

String or binary data would be truncated (stored procedure)

I get this error when run my stored procedure.This procedure works properly for some inputs. I set day in below code : 2016/08/24 00:00:00 In this case get error but if i change to 2016/08/25 00:00:00 works currently.In my stored procedure i call Table Base Function and get data from it .I Increase length of inputs in procedure and function but don't work .The type of variable that print number of it in Error is DateCall nvarchar(128) this is same in function and stored procedure.If code is right ? should works for any inputs.

EXEC    @return_value = [dbo].[ProcGetReportTRAINPDF]
        @StartDate = N'2016/08/24 00:00:00',
        @endDate = N'2016/08/31 23:59:59',
        @top = 50

Msg 8152, Level 16, State 13, Procedure ProcGetReportTRAINPDF, Line 92 String or binary data would be truncated. The statement has been terminated.

SP:

ALTER PROCEDURE [dbo].[ProcGetReportTRAINPDF](@StartDate  nvarchar(128),@endDate  nvarchar(128) , @top int)
AS 
BEGIN 
DECLARE @GetTrainRep TABLE
     (
          RajaID nchar(50)
           ,fk_serial nvarchar(50)
           ,CircularPeriod nvarchar(50)
           ,TrainNumber nvarchar(50)
           ,MoveDate nvarchar(50)
           ,WagonType nvarchar(50)
           ,WagonTypeName nvarchar(50)
           ,Degree nvarchar(50)
           ,WagonNumber nvarchar(50)
           ,CompartmentNumber nvarchar(50)
           ,SeatNumber nvarchar(50)
           ,RationCode nvarchar(50)
           ,RattionName nvarchar(50)
           ,SexCode nvarchar(50)
           ,StartStation nvarchar(50)
           ,startstationName nvarchar(50)
           ,EndStation nvarchar(50)
           ,EndStationName nvarchar(50)
           ,TicketSeries nvarchar(50)
           ,TicketNumber nvarchar(50)
           ,Movetime nvarchar(50)
           ,Name nvarchar(50)
           ,Family nvarchar(50)
           ,NationalCode nvarchar(50)
           ,Fk_sellerCode nvarchar(50)
           ,Fk_SaleCenterCode nvarchar(50)
           ,saleCenterName nvarchar(50)
           ,Telephone nvarchar(50)
           ,Register nvarchar(50)
           ,fk_TicketType nvarchar(50)
           ,fk_Tariff nvarchar(50)
           ,TariffName nvarchar(50)
           ,Formula1 nvarchar(50)
           ,Formula2 nvarchar(50)
           ,Formula3 nvarchar(50)
           ,Formula4 nvarchar(50)
           ,Formula5 nvarchar(50)
           ,Formula6 nvarchar(50)
           ,Formula7 nvarchar(50)
           ,Formula8 nvarchar(50)
           ,Formula9 nvarchar(50)
           ,Formula20 nvarchar(50)
           ,Formula19 nvarchar(50)
           ,Formula18 nvarchar(50)
           ,Formula17 nvarchar(50)
           ,Formula16 nvarchar(50)
           ,Formula15 nvarchar(50)
           ,Formula14 nvarchar(50)
           ,Formula13 nvarchar(50)
           ,Formula12 nvarchar(50)
           ,Formula11 nvarchar(50)
           ,FullPrice nvarchar(50)
           ,HalfPrice nvarchar(50)
           ,AxleCode nvarchar(50)
           ,PathCode nvarchar(50)
           ,OrderNumber nvarchar(50)
           ,Formula10 nvarchar(50)
           ,SaleId nvarchar(50)
           ,ServicesCode nvarchar(50)
           ,ServicesNo nvarchar(50)
           ,ServiesAmount nvarchar(50)
           ,TotalServices nvarchar(50)
           ,Amount nvarchar(50)
           ,ReduplicateID nvarchar(50)
           ,R2 nvarchar(50)
           ,[Status] nvarchar(50)
           ,PersonCode nvarchar(50)
           ,ReuplicateTicketNumber nvarchar(50)
           ,ReuplicateTicketSeries nvarchar(50)
           ,IsPrintAble nvarchar(50)
           ,TrainMessage nvarchar(50)
           ,CompanyName nvarchar(50)
           ,Isprinted nvarchar(50)
           ,statusName nvarchar(50)
           ,BarcodeImage image
           ,SecurityNumber nvarchar(50)
           ,servicetypename nvarchar(50)
           ,TimeOfArrival nvarchar(50)
           ,UserName nvarchar(50)
           ,DateCall nvarchar(128)
           ,DeparturId int
           ,ReserveDate nvarchar(50)
           ,IsDepartur bit
   )
INSERT INTO @GetTrainRep 
SELECT * from dbo.[FunGetReportTRAINPDF](@StartDate ,@endDate , @top  )
SELECT * FROM @GetTrainRep
END

Function :

ALTER FUNCTION [dbo].[FunGetReportTRAINPDF](@StartDate  nvarchar(128),
                                            @endDate   nvarchar(128),
                                            @top       INT)
RETURNS @GetAllRep TABLE
(RajaID                 NCHAR(50),
 fk_serial              NVARCHAR(50),
 CircularPeriod         NVARCHAR(50),
 TrainNumber            NVARCHAR(50),
 MoveDate               NVARCHAR(50),
 WagonType              NVARCHAR(50),
 WagonTypeName          NVARCHAR(50),
 Degree                 NVARCHAR(50),
 WagonNumber            NVARCHAR(50),
 CompartmentNumber      NVARCHAR(50),
 SeatNumber             NVARCHAR(50),
 RationCode             NVARCHAR(50),
 RattionName            NVARCHAR(50),
 SexCode                NVARCHAR(50),
 StartStation           NVARCHAR(50),
 startstationName       NVARCHAR(50),
 EndStation             NVARCHAR(50),
 EndStationName         NVARCHAR(50),
 TicketSeries           NVARCHAR(50),
 TicketNumber           NVARCHAR(50),
 Movetime               NVARCHAR(50),
 Name                   NVARCHAR(50),
 Family                 NVARCHAR(50),
 NationalCode           NVARCHAR(50),
 Fk_sellerCode          NVARCHAR(50),
 Fk_SaleCenterCode      NVARCHAR(50),
 saleCenterName         NVARCHAR(50),
 Telephone              NVARCHAR(50),
 Register               NVARCHAR(50),
 fk_TicketType          NVARCHAR(50),
 fk_Tariff              NVARCHAR(50),
 TariffName             NVARCHAR(50),
 Formula1               NVARCHAR(50),
 Formula2               NVARCHAR(50),
 Formula3               NVARCHAR(50),
 Formula4               NVARCHAR(50),
 Formula5               NVARCHAR(50),
 Formula6               NVARCHAR(50),
 Formula7               NVARCHAR(50),
 Formula8               NVARCHAR(50),
 Formula9               NVARCHAR(50),
 Formula20              NVARCHAR(50),
 Formula19              NVARCHAR(50),
 Formula18              NVARCHAR(50),
 Formula17              NVARCHAR(50),
 Formula16              NVARCHAR(50),
 Formula15              NVARCHAR(50),
 Formula14              NVARCHAR(50),
 Formula13              NVARCHAR(50),
 Formula12              NVARCHAR(50),
 Formula11              NVARCHAR(50),
 FullPrice              NVARCHAR(50),
 HalfPrice              NVARCHAR(50),
 AxleCode               NVARCHAR(50),
 PathCode               NVARCHAR(50),
 OrderNumber            NVARCHAR(50),
 Formula10              NVARCHAR(50),
 SaleId                 NVARCHAR(50),
 ServicesCode           NVARCHAR(50),
 ServicesNo             NVARCHAR(50),
 ServiesAmount          NVARCHAR(50),
 TotalServices          NVARCHAR(50),
 Amount                 NVARCHAR(50),
 ReduplicateID          NVARCHAR(50),
 R2                     NVARCHAR(50),
 [Status]               NVARCHAR(50),
 PersonCode             NVARCHAR(50),
 ReuplicateTicketNumber NVARCHAR(50),
 ReuplicateTicketSeries NVARCHAR(50),
 IsPrintAble            NVARCHAR(50),
 TrainMessage           NVARCHAR(50),
 CompanyName            NVARCHAR(50),
 Isprinted              NVARCHAR(50),
 statusName             NVARCHAR(50),
 BarcodeImage           IMAGE,
 SecurityNumber         NVARCHAR(50),
 servicetypename        NVARCHAR(50),
 TimeOfArrival          NVARCHAR(50),
 UserName               NVARCHAR(50),
 DateCall               NVARCHAR(128),
 DeparturId             INT,
 ReserveDate            NVARCHAR(50),
 IsDepartur             BIT

/******[TrainReserveId] = DeparturId*********/

)
AS
     BEGIN
         INSERT INTO @GetAllRep

                /****** Script for SelectTopNRows command from SSMS  ******/

                SELECT TOP (@top) [RajaID],
                                  [fk_serial],
                                  [CircularPeriod],
                                  [TrainNumber],
                                  [MoveDate],
                                  [WagonType],
                                  [WagonTypeName],
                                  [Degree],
                                  [WagonNumber],
                                  [CompartmentNumber],
                                  [SeatNumber],
                                  [RationCode],
                                  [RattionName],
                                  [SexCode],
                                  [StartStation],
                                  [startstationName],
                                  [EndStation],
                                  [EndStationName],
                                  [TicketSeries],
                                  [TicketNumber],
                                  [Movetime],
                                  [Name],
                                  [Family],
                                  [NationalCode],
                                  [Fk_sellerCode],
                                  [Fk_SaleCenterCode],
                                  [saleCenterName],
                                  [Telephone],
                                  [Register],
                                  [fk_TicketType],
                                  [fk_Tariff],
                                  [TariffName],
                                  [Formula1],
                                  [Formula2],
                                  [Formula3],
                                  [Formula4],
                                  [Formula5],
                                  [Formula6],
                                  [Formula7],
                                  [Formula8],
                                  [Formula9],
                                  [Formula20],
                                  [Formula19],
                                  [Formula18],
                                  [Formula17],
                                  [Formula16],
                                  [Formula15],
                                  [Formula14],
                                  [Formula13],
                                  [Formula12],
                                  [Formula11],
                                  [FullPrice],
                                  [HalfPrice],
                                  [AxleCode],
                                  [PathCode],
                                  [OrderNumber],
                                  [Formula10],
                                  [SaleId],
                                  [ServicesCode],
                                  [ServicesNo],
                                  [ServiesAmount],
                                  [TotalServices],
                                  [Amount],
                                  [ReduplicateID],
                                  [R2],
                                  [Status],
                                  [PersonCode],
                                  [ReuplicateTicketNumber],
                                  [ReuplicateTicketSeries],
                                  [IsPrintAble],
                                  [TrainMessage],
                                  [CompanyName],
                                  [Isprinted],
                                  [statusName],
                                  [BarcodeImage],
                                  [SecurityNumber],
                                  [servicetypename],
                                  [TimeOfArrival],
                                  [UserName],
                                  [DateCall],
                                  [DeparturId],
                                  dbo.PersianDate([ReserveDate]),
                                  [IsDepartur]
                FROM dbo.Payments
                     JOIN [dbo].[TrainReportTicket] ON dbo.Payments.ObjectIdDepartue = dbo.TrainReportTicket.DeparturId
                WHERE dbo.Payments.ReserveType = 2
                      AND dbo.Payments.Transactionsuccess = 1
                      AND CAST(@StartDate AS datetime) <= CAST([ReserveDate] AS datetime       )
                      AND CAST([ReserveDate] AS datetime) <= CAST(@endDate AS datetime);
         RETURN;
     END;

enter image description here[enter image description here][enter image description here]3enter image description here

Upvotes: 1

Views: 2314

Answers (1)

tweray
tweray

Reputation: 1003

What caused your Error message is the TrainMessage column, which in origin table been defined as NVARCHAR(800) while in both your function and proc it is getting truncated to NVARCHAR(50). You have the either match the size, or truncate it purposely by SUBSTRING() or LEFT() or something else in your function/proc.

I would still suggest to take consideration on people suggested in comment. Using string storing DateTime type is big red flag, and can eventually bite you back in the future. I would rather transfer them into right type on sight.

Upvotes: 1

Related Questions