**Syntax error converting varchar value to a column of data type int**

[jonnywah]

I am writing a SQL Server stored procedure that returns chemicals that match the chemicalIdString parameter. @chemicalIdString is a string of chemical ID's separated by commas.

Example -> '10, 9, 7'

When I run:

sp_GetChemical '10, 9, 7'


I get the following error:

Syntax error converting the varchar value '10, 9, 7' to a column of data type int.

My code:

CREATE PROCEDURE sp_GetChemical
@chemicalIdString varchar(250) = ''
AS
begin

select *
from chemical
where chemicalId IN (@chemicalIdString)
end
GO


I have tried changing the where clause to:

where chemicalId IN (cast (@chemicalIdString as Integer))

but I still get the error.

Please help. I am new to stored procedures.

 

[ClaireHCM]

ALTER PROCEDURE sp_GetChemical @chemicalIdString varchar(250) = ''
AS
begin
exec('select * from chemical where chemicalId INin ('+@chemicalIdString+')' )
end

 

[jonnywah]

Thank you Claire. =)