SELECT statement with IN and input variable in SQL server

Question

Having a table (Table1) with Columns -> Column1 int, Column2 int, Column3 varchar(128), Column4 char(11)

Need to create a stored procedure to get the details from Table1 based on the input parameter. Input parameter holds values with comma-separated. It has to be mapped with Column1

Create Procedure ProcedureName(@InputParams VARCHAR(MAX))
AS
BEGIN
    SELECT  Column2
            ,Column3 
            ,Column4
    FROM Table1
    WHERE Column1 IN (@InputParams)
    ORDER BY Column2
END

The below statement is throwing conversion error

"Conversion failed when converting the varchar value '123, 456, 789' to data type int."

EXEC ProcedureName @InputParams = '123, 456, 789'

Converting the SELECT statement to the dynamic query will work fine but will we able to fix the conversion issue in the static query. Please help me.

That's a text parameter, not a list of parameters. T-SQL has no array types so you can't specify an array of values to the `IN` clause. You'll have to either use a table-valued parameter and join with it or split the text parameter and join with the results. A TVP is safer, and definitely easier to create and use in T-SQL — Panagiotis Kanavos, Jan 12 '21 at 09:38
@PanagiotisKanavos Exactly!! Your comment just reiterates my answer. :) — Satwik Nadkarny, Jan 12 '21 at 09:45
@SatwikNadkarny identical questions have been asked and answered many times already. The only thing one can add to this question is that SQL Server 2012 is already out of support and the OP should migrate to a supported version — Panagiotis Kanavos, Jan 12 '21 at 09:46

score 0 · Answer 1 · edited Jan 12 '21 at 09:47

Well, you have a couple of options in this case :

Table-valued input parameter : For this, you'll have to create a UDT (user-defined table type) and use that as the parameter to your stored procedure.
Convert the comma-separated input string parameter into integers and then use them in the IN clause.

Not sure about your use-case, but generally, I would suggest going forward with the first option.

SELECT statement with IN and input variable in SQL server

1 Answers1