CODEWITHSUNDEEP
sql-serverstored-procedures
Chris
Chris

Reputation: 1475

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

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.

Upvotes: 1

Views: 6558

Answers (1)

gbn
gbn

Reputation: 432210

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.

Upvotes: 5

Related Questions