A long-troubled sql stored procedure (syntax error when converting to a column of data type int)

wolf0418

CREATE PROCEDURE GetDisplayNameByBatchAttibuteValues
(
@ListItemValue varchar (8000),
@ListDisplayName varchar (8000) output
)
AS

begin
declare @str varchar (8000)

 set @str = ''
 select @str = @str + ',' + ListDisplayName from ProductAttributeLookupListItems where ListItemValue in (@ListItemValue)

 set @ ListDisplayName = stuff (@ str, 1,1, '')
print @ListDisplayName
end
GO

@ListItemValue passes substrings like 14, 18, 22
error:
System.Data.SqlClient.SqlException: A syntax error occurred while converting the varchar value '14, 18,22 'to a column of data type int.

Please enlighten me! Thanks for your tears ~

lisongfox

What type of ListItemValue is in the database?

wolf0418

ListItemValue integer type, @ListItemValue is varchar (8000) passing multiple values, it seems that it will automatically become ('14, 18,22 ') in in, which is illegal, it should be in (14,18, twenty two)
How can I remove the automatic two `` number '', I have tried to replace it, and there is no good way, thank you for your advice

lisongfox

CREATE PROCEDURE GetDisplayNameByBatchAttibuteValues
(
@ListItemValue varchar (8000),
@ListDisplayName varchar (8000) output
)
AS

begin
declare @str varchar (8000)


declare @tb table (a int)
while charindex (',', @ s)> 0
begin
 insert @tb values ​​(cast (left (@ s, charindex (',', @ ListItemValue) -1) as int))
 set @s = stuff (@ListItemValue, 1, charindex (',', @ ListItemValue), '')
end
insert @tb values ​​(@ListItemValue)

 set @str = ''
 select @str = @str + ',' + ListDisplayName from ProductAttributeLookupListItems where ListItemValue in (select a from @tb)

 set @ ListDisplayName = stuff (@ str, 1,1, '')
print @ListDisplayName
end
GO

lisongfox

Use the following:


CREATE PROCEDURE GetDisplayNameByBatchAttibuteValues
(
@ListItemValue varchar (8000),
@ListDisplayName varchar (8000) output
)
AS

begin
declare @str varchar (8000)


declare @tb table (a int)
while charindex (',', @ ListItemValue)> 0
begin
 insert @tb values ​​(cast (left (@ s, charindex (',', @ ListItemValue) -1) as int))
 set @s = stuff (@ListItemValue, 1, charindex (',', @ ListItemValue), '')
end
insert @tb values ​​(@ListItemValue)

 set @str = ''
 select @str = @str + ',' + ListDisplayName from ProductAttributeLookupListItems where ListItemValue in (select a from @tb)

 set @ ListDisplayName = stuff (@ str, 1,1, '')
print @ListDisplayName
end
GO

wolf0418

Thank you,
Where set @s is undefined
Is it @str?

lisongfox

CREATE PROCEDURE GetDisplayNameByBatchAttibuteValues
(
@ListItemValue varchar (8000),
@ListDisplayName varchar (8000) output
)
AS

begin
declare @str varchar (8000)


declare @tb table (a int)
while charindex (',', @ ListItemValue)> 0
begin
 insert @tb values ​​(cast (left (@ ListItemValue, charindex (',', @ ListItemValue) -1) as int))
 set @s = stuff (@ListItemValue, 1, charindex (',', @ ListItemValue), '')
end
insert @tb values ​​(@ListItemValue)

 set @str = ''
 select @str = @str + ',' + ListDisplayName from ProductAttributeLookupListItems where ListItemValue in (select a from @tb)

 set @ ListDisplayName = stuff (@ str, 1,1, '')
print @ListDisplayName
end
GO

lisongfox

Sorry, no mode

wolf0418

Should be right
CREATE PROCEDURE GetDisplayNameByBatchAttibuteValues
(
@ListItemValue varchar (8000),
@ListDisplayName varchar (8000) output
)
AS

begin
declare @str varchar (8000)


declare @tb table (a int)
while charindex (',', @ ListItemValue)> 0
begin
 insert @tb values ​​(cast (left (@ ListItemValue, charindex (',', @ ListItemValue) -1) as int))
 set @ListItemValue = stuff (@ListItemValue, 1, charindex (',', @ ListItemValue), '')
end
insert @tb values ​​(@ListItemValue)

 set @str = ''
 select @str = @str + ',' + ListDisplayName from ProductAttributeLookupListItems where ListItemValue in (select a from @tb)

 set @ ListDisplayName = stuff (@ str, 1,1, '')

end
GO

But no result is returned, it is very strange

wolf0418

Thanks tolisongfox, the problem is solved.