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.

Answer 1

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