SQL Server Stored Procedure expects parameter, which was not supplied

Question

CREATE   PROCEDURE dbo.SP_GUILD_CHAR_CREATE 
    @i_guild_code           VARCHAR(10)     ,   --길드코드
    @i_character_name       VARCHAR(40)     ,   --캐릭터명
    @i_peerage_code         VARBINARY(1)    ,   --작위코드
    @o_sp_rtn               INT     OUTPUT  ,   --실행결과
             @2mx_user_no           varchar(50)

AS
DECLARE 
    @v_max_char_cnt         int,        --한길드당 최대 길드원 수
    @v_row_cnt              int
BEGIN   
    SET NOCOUNT ON
    SET @o_sp_rtn = -1
    SET @v_max_char_cnt = 50

    --입력값 검증
    IF  @i_guild_code IS NULL       OR 
        @i_character_name IS NULL   OR
        @i_peerage_code IS NULL     BEGIN
        SET NOCOUNT OFF
        SET @o_sp_rtn = -1
        RETURN
    END

    --길드중복가입 검사.
    IF EXISTS(  SELECT  1
                FROM    dbo.GUILD_CHAR_INFO WITH(NOLOCK)
                WHERE   character_name = @i_character_name )
    BEGIN
        SET NOCOUNT OFF
        SET @o_sp_rtn = -2
        RETURN
    END

    --한길드당 최대 길드원 수 검증
    SELECT  @v_row_cnt = count(*)
    FROM    dbo.GUILD_CHAR_INFO WITH(NOLOCK)
    WHERE   guild_code = @i_guild_code 
    IF @@Error <> 0 BEGIN
        SET NOCOUNT OFF
        SET @o_sp_rtn = -3
        RETURN
    END

    IF @v_max_char_cnt <= @v_row_cnt BEGIN
        SET NOCOUNT OFF
        SET @o_sp_rtn = -4
        RETURN
    END

    --길드원 등록

SET @2mx_user_no = (SELECT user_no FROM user_character 
WHERE character_name = @i_character_name)

    BEGIN TRAN
    INSERT INTO dbo.GUILD_CHAR_INFO
          (
            guild_code          ,
            character_name      ,
            peerage_code        ,
            ipt_time            ,
            upt_time                           ,
                                        user_no             
          )
    VALUES(
            @i_guild_code           ,
                                        @2mx_user_no        ,
            @i_character_name               ,
            ISNULL(CAST(@i_peerage_code AS smallint), 9)        ,
            getdate()                       ,
            getdate()
          )
    IF @@Error <> 0 
    BEGIN
        ROLLBACK TRAN
        SET NOCOUNT OFF
        SET @o_sp_rtn = -9
        RETURN 
    END

COMMIT TRAN
SET NOCOUNT OFF
SET @o_sp_rtn = 0
END
GO

Error in stored procedure:

SQL Stored Procedure expects parameter, which was not supplied.

I'm getting this error and I can't really understand what its wrong. Please try to be very detailed.

I don't know which parameter is not supplied and how should I fix it

Can you post the code you are using to call this stored procedure? — David, May 14 '13 at 21:17
SQl Server has something called Profiler that will capture the sql call that executes a proc. I assume most datbases have some type of profiling application they can use. If you capture the request to execute the proc, you will probably see right off what is worng. — HLGEM, May 14 '13 at 21:20
David asks the right question, but I'm going to hazard a guess. The OUT parameter was probably not supplied. But wouldn't the error message specify which parameter was missing? What version of SQL Server are you using? — Tim, May 14 '13 at 21:25
If you're using "raw" ADO.NET to call this, my guess would be that you're missing the `command.CommandType = CommandType.StoredProcedure;` assignment for your `SqlCommand` ... — marc_s, May 14 '13 at 21:26

score 1 · Answer 1 · edited May 23 '17 at 12:21

Your stored procedure expects five parameters:

@i_guild_code VARCHAR(10)
@i_character_name VARCHAR(40)
@i_peerage_code VARBINARY(1)
@o_sp_rtn INT OUTPUT
@2mx_user_no varchar(50)

None of them are supplied with default values by the procedure. Your calling code must provide these parameters in its sql command.

If you are calling this from a .NET class, you should ensure that all parameters are included in the SqlCommand. Here is a relevant question:

What's the best method to pass parameters to SQLCommand?

Be sure to set the .Direction property of the SqlParameter

If you are running them directly using sql script, you need to explicitly include your parameters after your execution call. See the MSDN Article for an example.

in your calling code, make sure you set the `.Direction` property. — Matthew, May 14 '13 at 23:15

SQL Server Stored Procedure expects parameter, which was not supplied

1 Answers1