Conversion failed when converting the varchar value '1,2' to data type int

Question

When I pass more than one id in sql server stored procedure it throws error like this==> Conversion failed when converting the varchar value '1,2' to data type int. and this is the sql query ===>SELECT * FROM SomeTAble WHERE colName in(@Ids)

Answer 1

If your stored procedure parameter expecting varchar(n), please have a look at accepted answer from T-SQL split string to split the value from a varchar.

Then you can apply the function in your stored procedure, you can simply change the query to something like this:

alter procedure your_stored_procedure @ids varchar(50) as begin select * from some_table where colName in (splitstring(@ids)) end go

However, if your stored procedure accepting int as parameter, please change it to varchar or any data type. Your stored procedure won't work because int will only accept single integer value.

Answer 2

You have to split string into rows first.

for do that you can use my sql Splitext function

Here is Installing Script

DROP FUNCTION IF EXISTS [dbo].[SplitText]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitText]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[SplitText]
( @TextForSplit varchar(1000)
, @SplitWith    varchar(5) = '',''
)       
RETURNS  @DataSource TABLE 
(
    ID  TINYINT identity,
    [Value] VARCHAR(500) NOT NULL
)
AS
BEGIN
        DECLARE @XML xml = N''<r><![CDATA['' + REPLACE(@TextForSplit, @SplitWith, '']]></r><r><![CDATA['') + '']]></r>''
        INSERT INTO @DataSource ([Value])
        SELECT RTRIM(LTRIM(T.c.value(''.'', ''NVARCHAR(128)'')))
        FROM @xml.nodes(''//r'') T(c)

        DELETE @DataSource WHERE [VALUE] = ''''
        RETURN    
END 
END
GO

And this is how to use it.

SELECT * FROM SomeTAble WHERE colName in( select value from Splittext('1,2,3,4,5' , ','))