I am working on a stored procedure that someone else built and I need to add to it. It the section the was previously written, part of the code is this:
Select @SQL = @SQL + '' + @dropdownlist + ' = ''' + @control + ''' and CMOPID in (' + @CMOPIDs + ')
Order by CMOPID, ProductCode'
Obviously before this statement the SQL variable was declared and some text was put in it. Then this combines that with these parameters and then makes a new statement that gets executed.
The section that I am adding looks like this:
FROM [vhacmncdb].[CDB].[dbo].[FormularyMaster] fm with (nolock)
join [vhacmncdb].[CDB].[info].[va_productlist] vp with (NOLOCK)
fm.productcode = vp.productcode
fm.productcode like '%'+@control+'%'
CMOPID in (@CMOPIDs)
The problem is that the previous code works fine. The new code works fine but only if one parameter is passed to the @CMOPIDs. With the original code, I can send multiples separated by a comma and it works as an in clause would. But if I try to pass multiples to the new code it returns nothing. It doesn't error or anything, but it returns no results which is not correct.