dataset using stored proc with multi select params

I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.

In the data tab I'm currently using "text" for command type and :

Code Snippet

declare @.sql nvarchar(2000)

set @.sql = '
EXEC [Monitor] '' + @.p_OfferStatus + '''

exec sp_executesql @.sql, N'@.p_OfferStatus VARCHAR(100)', @.p_OfferStatus = @.p_OfferStatus

when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?

That depends on your code within the proc. You will normally have to split the values up to a table variable to make them searchable as they will be passed as 01,2,3,4. COuld you send your code over ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hey, thanks for the reply. I can send the code, but you probobly dont need it, I'm just using
WHERE field IN(@.parameter)
|||

Code Snippet

SET @.v_sql = '
SELECT
a.clientname AS Client,
s.Suptype_ID,
s.[name] AS [The Name],
convert(int, GETDATE() - COALESCE(s.datelastprocessed, s.datecreated)) AS [Days Overdue],
CASE
WHEN ol. success_flag = 0 THEN ''FAILED''
WHEN ol. success_flag = 1 THEN ''SUCCESS''
ELSE ''''
END AS Processed,
s.datelastprocessed AS [Date Processed],
o.offerstatus_id AS [Offer Status],
CASE
WHEN s.advertiserlink LIKE ''%suptype_id=%'' THEN 2
WHEN ods.suptype_id IS NULL THEN 0
ELSE 1
END AS Auto
--ft.[File Name],
FROM #temp_suppressiontype s
LEFT OUTER JOIN #temp_1 ods ON ods.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_2 ol ON ol.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_3 p ON p.subtype_id = s.suptype_id
LEFT OUTER JOIN #temp_4 a ON a.clientkey_id = p.outsideadvertiser_clientkey_id
LEFT OUTER JOIN #temp_5 o ON o.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_6 os ON os.offerstatus_id = o.offerstatus_id
WHERE 1 = 1 ' +
CASE WHEN @.p_DaysOverdue IS NULL THEN
''
ELSE '
AND CONVERT(INT, GETDATE() - COALESCE(s.datelastprocessed, s.datecreated)) >= ' + CONVERT(VARCHAR, @.p_DaysOverdue)
END +
CASE WHEN @.p_OfferStatus IS NULL THEN
''
ELSE '
AND o.offerstatus_id IN (' + CONVERT(VARCHAR, @.p_OfferStatus) + ')'
END +
CASE WHEN @.p_Auto IS NULL THEN
''
ELSE '
AND CASE
WHEN s.advertiserlink LIKE ''%suptype_id=%'' THEN 2
WHEN ods.suptype_id IS NULL THEN 0
ELSE 1
END IN (' + CONVERT(VARCHAR, @.p_Auto) + ')'
END + '
ORDER BY 8 DESC, 1, 3 ASC'
PRINT @.v_sql
EXEC sp_executesql @.v_sql

|||

You cannot do this in your procedure, the parameters will be passed as a delimited string, this works if you are using directly a query in Reporting Service, but won′t work using the sttored procedure interface. See my Split function which you can use inside your SQL Server to chop the values and join them to the original query.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1331765&SiteID=1

Jens K. Suessmeyer

http://www.sqlserver2005.de