SQL Server 2008 Error 201 - Procedure Expects Parameter

Question

I have written the following USP:

ALTER PROCEDURE [dbo].[usp_M_InsertRegistration]
    @Reg_ID varchar(max),
    @Reg_UserID varchar(max),
    @Reg_Password varchar(max) ,
    @Reg_UserName varchar(max) ,
    @Reg_ContactNo varchar(max),
    @Reg_EmailId varchar(max) ,
    @City_ID varchar(max) ,
    @City_StateID varchar(max) ,
    @City_CountryID varchar(max) ,
    @Reg_SecurityQuestion varchar(max),
    @Reg_Answer varchar(max) ,
    @Reg_IsActive varchar(max) ,
    @Reg_Remark varchar(max) ,
    @Reg_CVPath varchar(max) ,
    @CreatedBy varchar(max) ,
    @CreatedDate varchar(max),
    @ModifiedBy varchar(max) ,
    @ModifiedDate varchar(max)
AS
BEGIN
    DECLARE @OutMsg VARCHAR(Max)
    DECLARE @intErrorCode INT

    IF EXISTS (SELECT 1 FROM M_Registration WHERE Reg_UserId = @Reg_UserID)--AND CreatedBy = @CreatedBy)
    BEGIN
            SET @OutMsg = 'false'
    END
    ELSE
    BEGIN
        INSERT INTO M_Registration(
            Reg_UserID,
            Reg_Password,
            Reg_UserName,
            Reg_ContactNo,
            Reg_EmailId,
            City_ID,
            City_StateID,
            City_CountryID,
            Reg_SecurityQuestion,
            Reg_Answer,
            Reg_IsActive,
            Reg_Remark,
            Reg_CVPath,
            CreatedBy,
            CreatedDate)
        VALUES(
            @Reg_UserID,
            @Reg_Password,
            @Reg_UserName,
            @Reg_ContactNo,
            @Reg_EmailId,
            @City_ID,
            @City_StateID,
            @City_CountryID,
            @Reg_SecurityQuestion,
            @Reg_Answer,
            @Reg_IsActive,
            @Reg_Remark,
            @Reg_CVPath,
            @CreatedBy,
            GETDATE())
        SET @Reg_ID = @@IDENTITY
        END
END
GO

When trying to execute the stored procedure through SQL Server to test, it displays the error:

Msg 201, Level 16, State 4, Procedure usp_M_InsertRegistration, Line 0
Procedure or function 'usp_M_InsertRegistration' expects parameter '@Reg_ID', which was not supplied.

I am unable to resolve this issue.

how are you trying to execute your sp?, are you passing all the parameters needed? — Lamak
1) Provide @Reg_ID to EXEC [dbo].[usp_M_InsertRegistration] @Reg_ID = Or set default in code @reg_Id NVARCHAR(MAX) = <value> — Lukasz Szozda
SET @Reg_ID = @@IDENTITY what is for at the end of stored procedure? — Lukasz Szozda
See What's the Point of Using VARCHAR(n) Anymore? - don't just use varchar(max) everywhere out of lazyness ...... - definitely NOT a good idea! — marc_s

M.Ali M.Ali · Accepted Answer · 2015-08-24T14:41:07

There are a couple things wrong with this procedure.

Variable @Reg_ID is of type varchar(max) where its purpose is to return the newly inserted identity value which is most probably Integer. So the datatype for this variable should be INT
The @Reg_ID is mentioned as input type parameter, since you are assigning it a value inside the procedure and most definitely want to know what it has been assigned to after procedure execution completes, you need to make it an OUTPUT parameter by mentioning the keyword OUTPUT next to it in procedure definition.
```
ALTER PROCEDURE [dbo].[usp_M_InsertRegistration]
      @Reg_ID INT OUTPUT,
       ........
```
You are using @@IDENTITY system function to capture the value of newly inserted identity value, @@IDENTITY will return the newly inserted identity value across any session any table. You need to use SCOPE_IDENTIT() function which will only return the newly inserted identity value in your session/scope.

SQL Server 2008 Error 201 - Procedure Expects Parameter

1 Answers