Error in executing Function - 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 2000 Forums

Transact-SQL (2000)

Error in executing Function

Author

Topic

samcneal
Starting Member

3 Posts

Posted - 2006-07-11 : 17:54:32

String or binary data would be truncated.
The statement has been terminated.

If I pass in ('A, B, C, D') it works perfectly; however, if I pass ('E413968C-xxxx-xxxx-xxxx-A86ACAC349CB, 5DB8E9CF-xxxx-xxxx-xxxx-E172E8EB6FDA') then I received the above error message. If I just passed in one of the values then it works. We need to pass in an array values to build the temp table.

I run the below code:

DECLARE @ParamList varchar(2000)

SELECT *
FROM db.rcudf_ConvertMetroTabletoList('E413968C-xxxx-xxxx-xxxx-A86ACAC349CB, 5DB8E9CF-xxxx-xxxx-xxxx-E172E8EB6FDA'
CREATE FUNCTION db.rcudf_ConvertMetroTabletoList(@ParamList varchar(2000))
RETURNS @retRelationTable TABLE (
Children varchar(36)
)
AS
BEGIN
DECLARE @Start int,
@Current int,
@ParamLength int,
@Value varchar(50)
SET @Start = 1
SET @ParamLength = len(@ParamList)
WHILE @Start < @ParamLength
BEGIN
SET @Current = CHARINDEX ( ',' ,@ParamList, @Start)
IF @Current <> 0
BEGIN
SET @Value = SUBSTRING (@ParamList, @Start, @Current - @Start)
SET @Start = @Current + 1
IF @Current <> 0
INSERT @retRelationTable
VALUES(@Value)
END ELSE
BEGIN
SET @Value = SUBSTRING (@ParamList, @Start, @ParamLength)
INSERT @retRelationTable
VALUES(@Value)
SET @Start = @Paramlength + 1
END
END
RETURN
select len(@paramlist) as len_paramlist
END

Sonya A. McNeal,
MCDBA, MCSE, MCT,...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-07-11 : 18:09:47

remove the space between your comma seperated input list items (or ltrim the value). The table you are returning has a column that is varchar(36) but the value you tried to put into it had a leading space which bumped the length to 37.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources