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;
[
][
]3
nvarchar) asDateTimetypes? This is always a big red flag. If the type needs to contain a date then useDate, if its a time useTime, if its a date with time then useDateTime2(or justDateTimeif running an earlier version of sql server).alt+F1.