anybody tell me the answer for this query

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

--[dbo].[SP_GET_CITY_BY_STATE] '1,2'
    -- =============================================
    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

Try the exec keyword to execute a script.

declare @cStatement varchar(max)
set @cStatement = 'SELECT TBL_CITY.CITY_NAME,TBL_CITY.CITY_ID FROM TBL_CITY WHERE TBL_CITY.STATE_ID IN(' + @STATE_ID1 + ')'

exec(@cStatement)