We cannot pass parameter value as string for integer column.
Please try this.
Solution:
Step 1: Create a function
CREATE function [dbo].[fn_StringToParameter]
(
@param nvarchar(max),
@delimiter char(1)
)
returns @t table (val nvarchar(max), seq int)
as
begin
set @param += @delimiter
;with a as
(
select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq
union all
select t + 1, charindex(@delimiter, @param, t + 1), seq + 1
from a
where charindex(@delimiter, @param, t + 1) > 0
)
insert @t
select substring(@param, f, t - f), seq from a
option (maxrecursion 0)
return
end
Step 2: Modify your stored Procedure
ALTER PROCEDURE [dbo].[SP_GET_CITY_BY_STATE]
@STATE_ID1 varchar =null
AS
BEGIN
SELECT TBL_CITY.CITY_NAME,TBL_CITY.CITY_ID FROM TBL_CITY WHERE TBL_CITY.STATE_ID
IN(SELECT val FROM dbo.fn_StringToParameter(@STATE_ID1, ','))
END