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?

Solution 1

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

Solution 2

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

Solution 3

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.

Comments

[DatumPoint]

I've been using SSRS daily for the last eight years (all versions since the SQL Server 2000 add-on that was released in 2004) and temp tables have been no problem in any version. I've never come across anything like "...SSRS does not like temp tables..." before. The problems described in the link above makes me think that there must be some other explanation for the observed phenomena.

[Unbound]

Temp tables inside SP is fine but not when used to write query inside SSRS

[DatumPoint]

Again, that is simply not correct. There must be some other factor at play.

[Unbound]

Sorry I do not understand what you mean. Can you you please elaborate and show why that is not correct and how you are getting it to work for you?