Calling dynamic SQL Server stored procedure - expects parameter which was not supplied

Question

I'm trying to call a stored procedure, the name of which is provided by looping through a table. The stored procedure is in another database.

However, I keep getting the message

Procedure or function 'sp_KPI_People_Added_Count' expects parameter '@Period', which was not supplied.

But it is being supplied.

I have set the statement and parameter definition to NVarChar and prefixed them with N. I've also tried this on the parameters themselves but apparently that's not required.

All the print output is as expected and if I call an execute with the text, it works fine.

So I'm stuck. Any pointers would be warmly welcomed.

Thanks,

Chris.

Declare @KPI_Value Decimal(14,4)
Declare @StoredProcedure NVarChar(200)
Declare @Periodic Char(1)
Declare @ExecSql NVarChar(200)
Declare @ParameterDefinition AS NVarChar(200)

Declare KPI_Cursor CURSOR LOCAL FOR
    SELECT SProc, Periodic from KPI where Section = 2
    FOR UPDATE OF Current_Value

Open KPI_Cursor

Fetch Next From KPI_Cursor into @StoredProcedure, @Periodic
Declare @Required_Period VARCHAR(5)
SET @Required_Period='MTD'

While @@FETCH_STATUS = 0 BEGIN
Set @KPI_Value = 0  -- Have tried with and without this

    If @Periodic = 'Z' BEGIN
        SET @ExecSQL = N'Prod.dbo.' + @StoredProcedure;
        /* Specify Parameter Format */
        SET @ParameterDefinition =  N'@Period VarChar(5), @Result_Type VarChar(10), @Result Decimal(14,4) OUTPUT';
        /* Execute Transact-SQL String */
        print @ExecSQL
        print @ParameterDefinition
        print @Required_Period
        print @KPI_Value
        EXECUTE sp_executesql @ExecSQL, @ParameterDefinition, @Required_Period, 'Result', @KPI_Value Output 
    END

    Fetch Next from KPI_Cursor into @StoredProcedure, @Periodic
END

Close KPI_Cursor
Deallocate KPI_Cursor

The stored procedures have parameters as follows:

ALTER PROCEDURE [dbo].[sp_KPI_People_Added_Count] 
    @Period VarChar(5),     -- MTD or YTD
    @Result_Type VarChar(10),   -- Summary, Result or Detail
    @Result Decimal(14,4) OUTPUT    -- The returned result
AS
....

The output is as follows:

Prod.dbo.sp_KPI_People_Added_Count
@Period VarChar(5), @Result_Type VarChar(10), @Result Decimal(14,4) OUTPUT
MTD
0.0000
Msg 201, Level 16, State 4, Procedure sp_KPI_Cust_Added_Count, Line 0
Procedure or function 'sp_KPI_People_Added_Count' expects parameter '@Period', which was not supplied.

You know you don't need sp_executesql in this case?

gbn
– gbn

2011-12-29 09:37:11 +00:00
Commented Dec 29, 2011 at 9:37 — gbn
– gbn, Commented Dec 29, 2011 at 9:37
Not good idea to name the procedure starting with sp_

Oleg Dok
– Oleg Dok

2011-12-29 09:42:10 +00:00
Commented Dec 29, 2011 at 9:42 — Oleg Dok
– Oleg Dok, Commented Dec 29, 2011 at 9:42

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

An alternative way...

EXEC has the little used form EXEC @module_name_var

So you can do this

If @Periodic = 'Z' BEGIN
    SET @module_name_var = N'Prod.dbo.' + @StoredProcedure;
    EXEC @module_name_var @Required_Period, 'Result', @KPI_Value Output 
END

One observation about your original code...

Use N'Result' for the 2nd parameter. sp_executesql says (my bold)

[ @param1= ] 'value1'

Is a value for the first parameter that is defined in the parameter string. The value can be a Unicode constant or a Unicode variable. There must be a parameter value supplied for every parameter included in stmt.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 29, 2011 at 9:41

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

The change to the Declare didn't help but using just Exec as above did. Thats great and thank you, but I can't see why the other form didn't work.

gbn Over a year ago

@Chris: I don't know why either. I wonder if it because you call a stored proc rather than execute simple DML

Collectives™ on Stack Overflow

Calling dynamic SQL Server stored procedure - expects parameter which was not supplied

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related