Stored Procedure Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure Error

Author

Topic

R.Prabu
Starting Member

33 Posts

Posted - 2008-05-22 : 14:49:53

This procudere displaying error 'String or binary data would be truncated.'

ALTER PROCEDURE [dbo].[Maintenance_ManagePropertyActioned]

(@sortCol as varchar(50),
@SortOrder as varchar(10),
@Status as varchar(20),
@Fdate as varchar(20),
@TDate as varchar(20)
)

As

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @ExecuteSQL AS Varchar(Max)

Create Table #TempContractorTable
(
MRNumber varchar(50),
CallDate varchar(25),
ProblemNature varchar(500),
Responsible varchar(50),
TypeOfRequest varchar(10),
ContractorCode varchar(50),
ContractorName varchar(50),
PropertyAddress varchar(500)
)

SET @ExecuteSQL=''

SET @ExecuteSQL = @ExecuteSQL + '(SELECT CC.MRNumber As ''MReqNo'', Convert(Varchar,CC.CallDate,107) +'', ''+ CONVERT(CHAR(8),CC.CallTime,8)+ ''(GMT)'' As ''CallDateTime'', '
SET @ExecuteSQL = @ExecuteSQL + 'CC.ProblemNature, CC.Responsible,CC.TypeOfRequest, CC.ContractorCode, TLM.Title +'' ''+ CT.Fname + '' '' + CT.Surname As ''ContractorName'', '
SET @ExecuteSQL = @ExecuteSQL + 'PM.ApartmentNo + '','' + PM.FloorNumber + '','' + PM.[HouseName / No] + '','' + PM.[Street Line1] + '','' + '
SET @ExecuteSQL = @ExecuteSQL + 'Replace(ISNULL(PM.[Street Line2],'''') ,ISNULL(PM.[Street Line2],''''),ISNULL(PM.[Street Line2],'''') + '','') + PM.[City / Town] + '','' + '
SET @ExecuteSQL = @ExecuteSQL + 'PM.[County / State] + '','' + PM.[Post / Zip code] + '','' + C.CountryName As ''Property Address'' '
SET @ExecuteSQL = @ExecuteSQL + 'FROM HSSPMS_Tbl_Callcentre_Compliants AS CC '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_PropertyMaster AS PM ON CC.PropertyId = PM.PropertyId '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Contractors AS CT ON CT.ContractorCode = CC.ContractorCode '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Country AS C ON PM.Country = C.CountryId '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Title AS TLM ON TLM.id = CT.Title '
SET @ExecuteSQL = @ExecuteSQL + 'LEFT OUTER JOIN dbo.HSSPMS_TblTenantMaster AS TM ON CC.TenantId = TM.TenantCode '
SET @ExecuteSQL = @ExecuteSQL + 'WHERE CC.JobStatus = 1) UNION ALL '

SET @ExecuteSQL = @ExecuteSQL + '(SELECT CC.MRNumber As ''Maintenance Req No'', Convert(Varchar,CC.CallDate,107) +'', ''+ CONVERT(CHAR(8),CC.CallTime,8)+ ''(GMT)'' As ''CallDateTime'', '
SET @ExecuteSQL = @ExecuteSQL + 'CC.ProblemNature, CC.Responsible,CC.TypeOfRequest, CC.ContractorCode, TLM.Title +'' ''+ FCT.Fname + '' '' + FCT.Surname AS ''ContractorName'', '
SET @ExecuteSQL = @ExecuteSQL + 'PM.ApartmentNo + '','' + PM.FloorNumber + '','' + PM.[HouseName / No] + '','' + PM.[Street Line1] + '','' + '
SET @ExecuteSQL = @ExecuteSQL + 'Replace(ISNULL(PM.[Street Line2],'''') ,ISNULL(PM.[Street Line2],''''),ISNULL(PM.[Street Line2],'''') + '','') + PM.[City / Town] + '','' + '
SET @ExecuteSQL = @ExecuteSQL + 'PM.[County / State] + '','' + PM.[Post / Zip code] + '','' + C.CountryName As ''Property Address'' '
SET @ExecuteSQL = @ExecuteSQL + 'FROM HSSPMS_Tbl_Callcentre_Compliants AS CC '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_PropertyMaster AS PM ON CC.PropertyId = PM.PropertyId '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_LandLordFulltimeEmployee AS FCT ON FCT.ContractorCode = CC.ContractorCode '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Country AS C ON PM.Country = C.CountryId '
SET @ExecuteSQL = @ExecuteSQL + 'INNER JOIN HSSPMS_Tbl_Title AS TLM ON TLM.id = FCT.Title '
SET @ExecuteSQL = @ExecuteSQL + 'LEFT OUTER JOIN dbo.HSSPMS_TblTenantMaster AS TM ON CC.TenantId = TM.TenantCode '
SET @ExecuteSQL = @ExecuteSQL + 'WHERE CC.JobStatus = 1) '

Insert InTo #TempContractorTable (MRNumber, CallDate, ProblemNature, Responsible, TypeOfRequest, ContractorCode, ContractorName, PropertyAddress)
EXEC (@ExecuteSQL)

SET @ExecuteSQL =' '

SET @ExecuteSQL = @ExecuteSQL + 'Select MRNumber From #TempContractorTable '

IF (@Status = 'Load')
BEGIN
SET @ExecuteSQL = @ExecuteSQL + ' Where datediff(day, CallDate, getdate()) > 5 ORDER BY '+ @SortCol + ' ' + @SortOrder + ''
END
IF (@Status = 'Search')
BEGIN
IF LEN(@Fdate)>0 AND LEN(@Tdate)>0
BEGIN
SET @ExecuteSQL=@ExecuteSQL + ' Where CallDate >= ''' + @FDate + ''' AND CallDate <= ''' + @TDate + ''' '
END
ELSE IF LEN(LTRIM(RTRIM(@Fdate)))>0
BEGIN
SET @ExecuteSQL=@ExecuteSQL + 'Where CallDate >= ''' + @FDate + ''' '
END
ELSE IF LEN(LTRIM(RTRIM(@Tdate)))>0
BEGIN
SET @ExecuteSQL=@ExecuteSQL + 'Where CallDate <= ''' + @TDate + ''' '
END
SET @ExecuteSQL=@ExecuteSQL + 'ORDER BY '+ @SortCol + ' ' + @SortOrder + ' '

END

--EXEC (@ExecuteSQL)
PRINT (@ExecuteSQL)
END

any one give me a solution

Regards,
Prabu R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 14:53:55

This is because you are trying to insert a value into a varchar field which has lesser length and cannot accomodate it. Suggest you to the check the lengths of values from fields in SELECT list and compare them to field lengths of temporary table to fix the inconsistency.

R.Prabu
Starting Member

33 Posts

Posted - 2008-05-22 : 15:14:35

Thank you

Regards,
Prabu R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources