Procedure or function has too many arguments specified SSRS

Question

For some reason I am able to run my SELECT statement in SSMS, but when I use it in SSRS then it says Procedure or function has too many arguments specified. If I choose only one parameter - it gives me the result. But if I choose 2 or more - I got an error.

This is my whole code:

DECLARE @ClassCode varchar(max) = '31439,739889'
CREATE TABLE #PolicyNumbers  (PolicyNumber varchar(50))
INSERT INTO #PolicyNumbers SELECT  PolicyNumber FROM tblClassCodesPlazaCommercial T1
WHERE NOT EXISTS (SELECT 1 FROM tblClassCodesPlazaCommercial T2  
WHERE  T1.PolicyNumber = T2.PolicyNumber
        AND ClassCode  IN 
        (SELECT * FROM [dbo].[StringOfStringsToTable](@ClassCode,','))) 
; WITH Earned_to_date AS (
   SELECT Cast('11-30-2016' AS DATE) AS Earned_to_date
), policy_data AS (
    SELECT
        PolicyNumber
,       Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
,       Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
,       WrittenPremium
        FROM PlazaInsuranceWPDataSet pid
        WHERE  NOT EXISTS (SELECT PolicyNumber FROM #PolicyNumbers pn WHERE pn.PolicyNumber = pid.PolicyNumber )
        AND State IN ('CA','NV','AZ') 
        
) 

, digits AS (
SELECT digit
   FROM (VALUES (0), (1), (2), (3), (4)
,      (5), (6), (7), (8), (9)) AS z2 (digit)
), numbers AS (
SELECT 1000 * d4.digit + 100 * d3.digit + 10 * d2.digit + d1.digit AS number
    FROM digits AS d1
    CROSS JOIN digits AS d2
    CROSS JOIN digits AS d3
    CROSS JOIN digits AS d4
), calendar AS (
SELECT
    DateAdd(month, number, '1753-01-01') AS month_of
,   DateAdd(month, number, '1753-02-01') AS month_after
    FROM numbers
), policy_dates AS (
SELECT
   PolicyNumber
,   CASE
        WHEN month_of < PolicyEffectiveDate THEN PolicyEffectiveDate
        ELSE month_of
    END AS StartRiskMonth
,   CASE
       WHEN PolicyExpirationDate < month_after THEN PolicyExpirationDate
       WHEN Earned_to_date.Earned_to_date < month_after THEN Earned_to_date
       ELSE month_after
    END AS EndRiskMonth
,   DateDiff(day, PolicyEffectiveDate, PolicyExpirationDate) AS policy_days
,   WrittenPremium
    FROM policy_data
    JOIN calendar
        ON (policy_data.PolicyEffectiveDate < calendar.month_after
        AND calendar.month_of < policy_data.PolicyExpirationDate)
    CROSS JOIN Earned_to_date
    WHERE  month_of < Earned_to_date
)
SELECT      PolicyNumber,
            --ClassCode,
            Year(StartRiskMonth) as YearStartRisk, 
            Month(StartRiskMonth) as MonthStartRisk,
            c.YearNum,c.MonthNum,
            convert(varchar(7), StartRiskMonth, 120) as RiskMonth,
            sum(WrittenPremium * DateDiff(day, StartRiskMonth, EndRiskMonth) / policy_days) as EarnedPremium
FROM        tblCalendar  c
LEFT  JOIN policy_dates l ON c.YearNum=Year(l.StartRiskMonth) and c.MonthNum = Month(l.StartRiskMonth) AND l.StartRiskMonth BETWEEN '01-01-2012' AND  '10-31-2016'
WHERE c.YearNum Not IN (2017) --and PolicyNumber = 'PACA1000191-00'
GROUP BY    convert(varchar(7), StartRiskMonth, 120),
            Year(StartRiskMonth) , Month(StartRiskMonth),
            c.YearNum,c.MonthNum,PolicyNumber
ORDER BY    PolicyNumber,c.YearNum,c.MonthNum
DROP TABLE #PolicyNumbers

I am using the split function that works fine:

ALTER FUNCTION [dbo].[StringOfStringsToTable]
    (
        @Strings varchar(8000),
        @Separator char(1)
    )
RETURNS @StringTable table (String varchar(500))
AS
    BEGIN

        DECLARE @String varchar(500), @Pos int

        SET @Strings = LTRIM(RTRIM(@Strings))+ @Separator
        SET @Pos = CHARINDEX(@Separator, @Strings, 1)

            WHILE @Pos > 0
            BEGIN
                SET @String = LTRIM(RTRIM(LEFT(@Strings, @Pos - 1)))

                IF @String <> '' INSERT INTO @StringTable VALUES (@String)

                SET @Strings = RIGHT(@Strings, LEN(@Strings) - @Pos)
                SET @Pos = CHARINDEX(@Separator, @Strings, 1)
            END

    RETURN
    END
    

This is the result set in SSMS:

And there is an error:

In a query designer in SSRS if I declare variable at the top with multiple value parameter than it works.

DECLARE @ClassCode varchar(100) = '31439,739889,33528,40199,21289,40427,42594,5283,5251,34489' 

But if I take off that statement than I receive the same error. What is going on here?

Answer 1

Writing for clarity as I believe this thread came to a wrong conclusion with temp tables.

I believe the problem here (I was having the same problem) is the spaces between the commas. When you put in a string with no spaces and just commas it works, but when you put in a string in your parameters section (For example if you copy paste) usually that is spaced out in different lines. When pulling that back into comma delimited values, SSRS adds a space between each one. Then it doesn't work. I'm not sure what exactly is going on behind the scenes but I know with the spaces it is broken and without them it works. This explains all of the cases you presented. You also found the solution to this using the join in the parameters! For my case and probably most similar cases the code is simply join(parameter, ", ")

Using that you can paste values with or without a space and it should work! Hope this clears up any confusion!

Answer 2

It worked by using JOIN(!Parameters!someparameter.Value, ",") in my report. The report called SP, and inside SP the parameter is parsed by Split table function and then joining column to act as filter.

Answer 3

Thank you guys for your recommendations. I will definately wrap my code into SP and quit using #TempTables when working with SSRS. But also I found a solution for my problem by modifiing the parameter on my dataset.So instead of default parameter @Parameters!ClassCode.Value I used JOIN function:

=Join(Parameters!ClassCode.Value,",")

Here is the link to additional info:

https://technicalreflections.wordpress.com/2011/05/12/ssrs-parameters-and-sql-functionsstored-procedures/

Thanks for help

Answer 4

I agree with the previous answer. The temp table within your script is a problem.

I believe you will make your life much easier if you make your script into a Stored Procedure on the server. Then create your dataset in SSRS by connecting to the stored procedure. It will handle the multiple parameters just fine then.

Answer 5

I know from experience that SSRS does not like temp tables. tables that start with a single hash (#).

Please convert that into a derived table or a global table and try again.

Here is a link to some examples