How to pass multiply values parameter to a procedure

Question

I want to pass multiply values parameter to my procedure and use it as a filter. My parameter is called @Month and it's datatype is NVARCHAR(MAX) in the procedure. I have used filter as

WHERE (cal.CalendarYear = @Year) AND (cal.MonthId IN (@Month))

and also tried STRING_SPLIT function. However, when I run my report, it return an error Conversion failed when converting the nvarchar value '1,2,3,4,5,6,7,8,9,10,11,12' to data type int.

See my Answer on your previous related question. It may save you a lot of work. stackoverflow.com/questions/45012713/… — Alan Schofield

nico boey nico boey · Accepted Answer · 2017-07-11T08:29:54

you cannot give a varchar string with comma separated values, and expect the query to process it as a 'in' list. The way that would work is when you would concatenate your sql statement in the stored procedure, and then execute the sql string with sp_executesql. Using string_split is one way of doing it, but you have to be aware that it gives you a table with varchars, not integers. For completeness I should mention that the most 'robust' way of doing this is passing a table type variable to your stored procedure, as described here: How to pass table value parameters to stored procedure from .net code. With the split_string, you could try something like this:

select
 -- your fields
from
    manytables mt
    join string_split(@Month,',') months on cast(months.value as int) = cal.monthId
where 
    cal.Calenderyear = @year

How to pass multiply values parameter to a procedure

1 Answers