I get an error (Incorrect syntax near ','.) when I choose more than one value
for any of the multi-valued report parameters in the below query. All
parameters, except scheduled_date and tracking_number, are defined as
multi-value. It may be a problem with strings appending. Any suggestions?
SELECT convert(varchar(20),D.deployment_id) AS deployment_id,
D.tracking_number, B.alternate_id, D.name,
CASE D.status WHEN 'PARTIAL_SENT' THEN 'SENDING' ELSE D.status END AS
status, D.sent_date, D.scheduled_date, D.owner_user_id
FROM o_dpl_deployment AS D INNER JOIN
o_bas_brand AS B ON D.environment_id = B.environment_id
INNER JOIN
o_dpl_deployment_type AS DT ON D.deployment_type_id = DT.deployment_type_id
AND B.environment_id = DT.environment_id
WHERE ((D.name IN (@name)) OR (@name = '-1'))
AND ((D.tracking_number LIKE @tracking_number) OR
(@tracking_number = ''))
AND ((D.promo_code IN (@promo_code)) or (@promo_code = '-1'))
AND ((D.scheduled_date >= @scheduled_date AND
D.scheduled_date < DATEADD(month,1,@scheduled_date))
OR (@scheduled_date = convert(datetime,'9499-12-31') AND D.scheduled_date
[quoted text, click to view] >= CAST(CONVERT(varchar,GETDATE(),110) as Datetime))
OR (@scheduled_date = convert(datetime,'9499-12-30') AND D.scheduled_date
>= GETDATE() - 1 AND D.scheduled_date < GETDATE())
OR (@scheduled_date = convert(datetime,'9499-12-29') AND D.scheduled_date
>= GETDATE() - 3 AND D.scheduled_date < GETDATE())
OR (@scheduled_date = convert(datetime,'9499-12-28') AND D.scheduled_date
>= GETDATE() - 7 AND D.scheduled_date < GETDATE())
OR (@scheduled_date = convert(datetime,'9499-12-27') AND D.scheduled_date
>= GETDATE() - 14 AND D.scheduled_date < GETDATE())
OR (@scheduled_date = convert(datetime,'9499-12-26') AND D.scheduled_date
>= GETDATE() - 30 AND D.scheduled_date < GETDATE())
OR (@scheduled_date = convert(datetime,'9499-12-25') AND D.scheduled_date
>= GETDATE() - 60 AND D.scheduled_date < GETDATE())
OR (@scheduled_date = convert(datetime,'9599-12-31')))
AND ((DT.name IN (@deployment_type_name)) OR
(@deployment_type_name = '-1'))
AND ((B.name IN (@brand_name)) OR (@brand_name = '-1'))
AND ((D.owner_user_id IN (@owner_user_id)) OR
(@owner_user_id = '-1'))
AND ((D.created_by IN (@created_by)) OR (@created_by = '-1'))
AND ((D.status IN (@status)) OR (@status = '-1'))
AND (D.status = 'SENT' OR D.status = 'PARTIAL_SENT')
UNION
SELECT '-1', '-1', '-1', '-1', '-1', '1753-12-31', '1753-12-31', '-1'
ORDER BY D.scheduled_date DESC
