SQLTeam.com | Weblogs | Forums

Concatenate Function for Gathering Records

I have the below function and stored procedure. I attached a screenshot. I'm trying to concatenate filenames into one field.

I get this error:
Msg 8114, Level 16, State 5, Line 23 "INSERT INTO Photos (Sku, Photos)"
Error converting data type varchar to numeric.

Individually, both function and SP parse correctly. The fields in the target Table: Photos are Sku varchar(10) and Photos varchar(MAX).

Can anyone give me a clue as to what I'm doing wrong?

Function
(
@Sku varchar(10)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @R varchar(MAX)

SELECT @R = ISNULL(@R + '
', '') + Sku + SortKey + '. ' + FileNam
FROM PhotosHighRes
WHERE Sku = @Sku
ORDER BY SortKey
RETURN @R

END

Stored Procedure
INSERT INTO Photos (Sku, Photos)

SELECT fctConcatPhotos(@Sku) as Sku, FileNam

FROM
(
SELECT
Sku, FileNam, SortKey
FROM
PhotosHighRes
GROUP BY
Sku, FileNam, SortKey
HAVING Sku = @Sku
) i

thanks!

what data types are Sku, Photos columns

Replace the above with
', '') + Sku + CAST(SortKey AS VARCHAR(32)) + '. ' + FileNam

Much appreciated! However, I'm now getting a new error. See below.

Msg 8152, Level 16, State 10, Line 23 "INSERT INTO Photos (Sku, Photos)"
String or binary data would be truncated.
The statement has been terminated.

My table attributes are:
[Sku] varchar(10)
[Photos] varchar(MAX).

I assume varchar(MAX) will accept a huge amount of data, correct?

Thanks!

Varchar (100) + varchar (max)

Here what happens
Result data type
Varchar(100) i think

There is something like this that happens
Not sure
How to check .. test ..is another skill process

I'm not sure what you were trying to relate. However, I Googled for varchar(MAX) and it should hold 2GB which is far more than I need. I tried changing the table and variable varchar(MAX) to varchar(8000) and I get the same truncating error on the INSERT line.

thanks!

Test how long the data you are trying to insert is by changing your query as shown below

SELECT
	MAX(LEN(fctConcatPhotos(@Sku))) AS MaxLen_SKU,
	MAX(LEN(FileNam) AS MaxLen_FileNam
FROM
(
    SELECT Sku,
           FileNam,
           SortKey
    FROM PhotosHighRes
    GROUP BY Sku,
             FileNam,
             SortKey
    HAVING Sku = @Sku
) i;

Now compare that with what the size of the columns in your Photos table are. You can do that either by looking at the columns in the object explorer, or via this query:

EXEC sp_help 'Photos'

What I suspect is that the size of one or the other - SKU or Photos column is too small. If that is the case, you will need to expand the size of that column accordingly.

My results for:
SELECT
MAX(LEN([jsk_admin].fctConcatPhotos)) AS MaxLen_SKU,
MAX(LEN(FileNam)) AS MaxLen_FileNam

Was:
MAXLen_Sku = 629
MAXLen_FileName = 34

Using: EXEC sp_help 'Photos'
Sku length = 10
Photos length = -1

I created table script to window:

CREATE TABLE [jsk_admin].[Photos](
[Sku] varchar NOT NULL,
[Photos] varchar NULL,
CONSTRAINT [PK_Photos] PRIMARY KEY CLUSTERED
(
[Sku] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

What in the world am I doing wrong? I would expect @Sku to be 5 characters, not 629. I cut n pasted a test FileName concatenated and it's under 500 characters.

Thanks!

show us fctConcatPhotos

Here's the function. I made a couple edits from the version posted above.

(
@Sku varchar(10)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @R varchar(MAX)

SELECT @R = ISNULL(@R + '
', '') + Sku + CAST(SortKey AS VARCHAR(32)) + '. ' + FileNam
FROM PhotosHighRes
WHERE Sku = @Sku
ORDER BY SortKey
RETURN @R

END

thanks!

do this please

select len(FileNam) from PhotosHighRes order by 1 desc

I get 333 results ranging from 37 down to 8 for the whole table.

When I filter it down to the one sku I've been testing with, I get 17 results ranging from 34 down to 13.

That would be the filename length as expected for reach record.

Hi

I tried to do this ..
hope this helps :slight_smile: :slight_smile:

i used FOR XML
different ways to concatenate are there

Web Site Article .. diff ways to concatenate ..

https://www.mytecbits.com/microsoft/sql-server/concatenate-multiple-rows-into-single-string

drop create data ...
drop table PhotosHighRes
go

drop table Photos 
go 


create table PhotosHighRes 
(
sku varchar(100),
FileName varchar(100),
SortKey int
)
go 

create table photos 
(
SKu varchar(100),
Photos varchar(max) 
)
go 

insert into PhotosHighRes select 'skua','SkuA_Main_Front_wMic.jpg', 1 
insert into PhotosHighRes select 'skua','SkuA_Front_Left_wMic_wDevice.jpg', 2 
insert into PhotosHighRes select 'skua','SkuA_Main_Front_woMic.jpg',3 
insert into PhotosHighRes select 'skua','SkuA_Front_Left_woMic_wDevice.jpg', 4 
insert into PhotosHighRes select 'skua','SkuA_Front_Right_wMic.jpg', 5 
insert into PhotosHighRes select 'skua','SkuA_Front_Right_woMic.jpg', 6 
insert into PhotosHighRes select 'skua','SkuA_Front_Left_wMic.jpg', 7 
insert into PhotosHighRes select 'skua','SkuA_Front_Left_woMic.jpg', 8 
insert into PhotosHighRes select 'skua','SkuA_Main_Back_wMic.jpg', 9 
insert into PhotosHighRes select 'skua','SkuA_Main_Back_woMic.jpg', 10 
insert into PhotosHighRes select 'skua','SkuA_Back_Right_wMic.jpg', 11 
insert into PhotosHighRes select 'skua','SkuA_Back_Right_woMic.jpg', 12 
insert into PhotosHighRes select 'skua','SkuA_Back_Left_wMic.jpg', 13 
insert into PhotosHighRes select 'skua','SkuA_Back_Left_woMic.jpg', 14 
insert into PhotosHighRes select 'skua','SkuA_Top_wMic.jpg', 15 
insert into PhotosHighRes select 'skua','SkuA_Top_woMic.jpg', 16 
insert into PhotosHighRes select 'skua','SkuA_Acc.jpg', 17 
go 

select * from PhotosHighRes
create function ...Fctconcatphotos
CREATE FUNCTION Fctconcatphotos (@Sku VARCHAR(10)) 
returns VARCHAR(max) 
AS 
  BEGIN 
      RETURN 
        (SELECT FileName = Stuff((SELECT ',' + filename 
                                  FROM   photoshighres 
                                  WHERE  sku = @Sku 
                                  ORDER  BY sortkey 
                                  FOR xml path('')), 1, 1, '')) 
  END
SQL to call FUCTION insert Photos table
DECLARE @Sku VARCHAR(100) = 'skua' 

INSERT INTO photos 
            (sku, 
             photos) 
SELECT @sku, 
       dbo.Fctconcatphotos(@Sku) 

SELECT * 
FROM   photos

The code you posted initially is concatenating several SKU's. You are then inserting that concatenated string to the table Photos. Is that what you intended to do? When you concatenate, it is quite possible that the concatenated string is long.

Run this, and you will see what you would be inserting into the table:

SELECT
	fctConcatPhotos(@Sku) as SKU,
	FileNam
FROM
(
    SELECT Sku,
           FileNam,
           SortKey
    FROM PhotosHighRes
    GROUP BY Sku,
             FileNam,
             SortKey
    HAVING Sku = @Sku
) i;

My sense is that, that is not what you were trying to insert into the jsk_admin.Phots table. But, if that is indeed what you want to insert into the jsk_admin.Photos, change your table as follows:

ALTER TABLE jsk_admin.Photos ALTER COLUMN Sku VARCHAR(1024) NOT NULL;
ALTER TABLE jsk_admin.Photos ALTER COLUMN Photos VARCHAR(128) NULL;

As a general rule, when you use VARCHAR data type, ALWAYS specify a length. Otherwise it defaults to something (31 or 32, or 1 or something depending on the context).

what is this and why do you even need a function
@R will always be NULL so why check and do that weird stuff, unless it is some sort of trick.

DECLARE @R varchar(MAX)

SELECT @R = ISNULL(@R + '
', '') 

To ALL - I really appreciate your input. I never would have figured this out without everyone's input. I tried the XML route and just ran into more issues dealing with carriage return codes. My intent was to concatenate the filenames as a string and insert into a table for convenient, speedy access to the data in more involved queries. Below is the completed function and stored procedure that is working for what I need.

Side Note: The below is created for one sku at a time. A couple edits and I run it for hundreds of skus.

FUNCTION
(
@Sku varchar(10)
)
RETURNS varchar(MAX)
AS
BEGIN
DECLARE @R varchar(MAX)

SELECT @R = ISNULL(@R + Char(13) + Char(10), '') + CAST(SortKey AS VARCHAR(32)) + '. ' + FileNam
FROM PhotosHighRes
WHERE Sku = @Sku
ORDER BY SortKey
RETURN @R

END

STORED PROCEDURE
@Sku varchar(10)

AS

DELETE FROM Photos

INSERT INTO Photos (Sku, Photos)

SELECT Sku, fctConcatPhotos(@Sku) as Photos

FROM
(
SELECT Sku
FROM PhotosHighRes
GROUP BY Sku
) i

Sincerely, thanks again!