Welsh Corgi (1/24/2012)
wolfkillj (1/24/2012)
Your splitter function returns the expected results, but the code in the stored proc should select the column Item from the function in the subselect in the WHERE clause, not State. Does that help?The function does not return the required results and there is a SELECT in the WHERE Clause.
WHERE Comp.Status = 'A'
AND State IN (SELECT State FROM dbo.Split(@State, ','))
Look at this line in the definition of your splitter function dbo.Split_Multi_Value_Params() that you posted above:
RETURNS @IDTable TABLE (Item NVARCHAR(100) collate database_default )
The function returns a table with a single column named Item. The code in your proc - WHERE State IN (SELECT State FROM dbo.Split_Multi_Value_Params(@State, ',')) - would generate error 207 - invalid column name 'State' (unless of course your function dbo.Split() in the code snippet you just posted is different from the function dbo.Split_Multi_Value_Params() that you previously posted). Have you ascertained that the code in your proc SELECTs the correct column name from the function?
Have you tested the function like this:
SELECT Item FROM dbo.Split_Multi_Value_Params('AZ,TN,NY', ',')
to see if it returns the expected output (as I did before I stated that it does)?
Do you get results when you EXECUTE the stored procedure directly in SSMS (or your preferred client software)?
If you confirm that you have corrected the small but significant mistake in the stored proc code, that the function returns a table with a column named Item populated the values from the delimited string, that you get results when you EXECUTE the stored procedure directly, and that you still do not get any results in your SSRS report, please post more information about how you are generating the report (i.e., in BIDS, with SSRS Report Manager, with the .NET Report Viewer control, by passing paramters to SSRS in the URL, etc.) and what happens when you try to view the report (e.g., an error message appears, the report appears to be running but never completes, etc.). Also, if you can SELECT @@ERROR on the same connection after you try to run the report, what is the output?
Jason Wolfkill