1

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

9
  • Tag the dbms used (perhaps SQL Server?) Commented Aug 31, 2016 at 8:48
  • I use sql server 2014 Commented Aug 31, 2016 at 8:50
  • line 92 : DateCall nvarchar(128) Commented Aug 31, 2016 at 8:51
  • side note - Why are you using strings (nvarchar) as DateTime types? This is always a big red flag. If the type needs to contain a date then use Date, if its a time use Time, if its a date with time then use DateTime2 (or just DateTime if running an earlier version of sql server). Commented Aug 31, 2016 at 9:03
  • 1
    Could you display the structure of the tables that you are extracting data from? It will be nice to be screenshot of their columns names and types - select the table name and press alt+F1. Commented Aug 31, 2016 at 10:10

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.