SQL Server stored procedure expects parameter which was not supplied

Question

CREATE TABLE [dbo].[review]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [uID] [varchar](6) NOT NULL,
    [pID] [int] NOT NULL,
    [email] [nvarchar](255) NOT NULL,
    [review] [nvarchar](3000) NULL,
    [refURL] [nvarchar](2083) NOT NULL,
    [refID] [nvarchar](100) NOT NULL,
    [cDate] [datetime] NOT NULL,

    CONSTRAINT [PK_review] 
        PRIMARY KEY CLUSTERED ([id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[review] 
    ADD CONSTRAINT [DF_review_uID] DEFAULT (LEFT(NEWID(), (6))) FOR [uID]
GO

ALTER TABLE [dbo].[review] 
    ADD CONSTRAINT [DF_review_cDate] DEFAULT (GETDATE()) FOR [cDate]
GO

I wrote this stored procedure:

ALTER PROCEDURE [dbo].[spReview] 
    @id INT = 0,
    @uID VARCHAR(6),
    @pID INT = 0,
    @email NVARCHAR(255),
    @review NVARCHAR(3000),
    @refURL NVARCHAR(2083),
    @refID NVARCHAR(100),
    @cDate DATETME = NULL,
    @OPERATION NVARCHAR(50) = ''
AS
    IF @OPERATION = 'Insert'
    BEGIN
        DECLARE @inserted TABLE ([uID] VARCHAR(6));

        INSERT INTO review ([pID], [email], [review], [refURL], [refID])
        OUTPUT INSERTED.[uID] INTO @inserted
        VALUES (@pID, @email, @review, @refURL, @refID)

        SELECT *
        FROM @inserted
    END
    ELSE IF @OPERATION = 'Delete'
    BEGIN
        DELETE FROM review
        WHERE id = @id
    END
    ELSE IF @OPERATION = 'Update'
    BEGIN
        UPDATE review
        SET pID = @pID,
            email = @email,
            review = @review,
            refURL = @refURL,
            refID = @refID
        WHERE id = @id
  END

uID : left(newid(),(6)) and cDate : getdate() set default value

DECLARE @return_value int

EXEC    @return_value = [dbo].[spReview]
        @id = N'29',
        @OPERATION = N'Delete'

SELECT  'Return Value' = @return_value

GO

I get this error when I execute the delete query:

Procedure or function 'spReview' expects parameter '@uID', which was not supplied

I tried debugging, I can't figure out where I made a mistake. Where did I make a mistake?

---

ELSE IF @OPERATION = 'Delete'
    BEGIN
        DELETE FROM review
        WHERE id = @id
END

Just waiting for '@id' parameter, it doesn't need '@uID'

Answer 1

This is your code:

ALTER PROCEDURE [dbo].[spReview] 
    @id INT = 0,
    @uID VARCHAR(6),
    @pID INT = 0,
    @email NVARCHAR(255),
    @review NVARCHAR(3000),
    @refURL NVARCHAR(2083),
    @refID NVARCHAR(100),
    @cDate DATETME = NULL,
    @OPERATION NVARCHAR(50) = '' 

For every parameter without an "=" sign, you need to provide a value. In your case, it means that you need to provide at least the following parameters:

    @uID VARCHAR(6),
    @email NVARCHAR(255),
    @review NVARCHAR(3000),
    @refURL NVARCHAR(2083),
    @refID NVARCHAR(100),

So either you provide these parameters when calling the procedure, or you rewrite your procedure such that you don't need these parameters.

Answer 2

As mentioned, multiple times, this should be 3 separate SPs, this way you only need the parameters you need for that operation:

CREATE PROC dbo.Review_Delete @id int AS
BEGIN 

    DELETE FROM dbo.review
    WHERE id = @id;
END;
GO

CREATE PROC dbo.Review_Insert @pID int, @email nvarchar(255), @review nvarchar(3000), @RefURL nvarchar(2083), @RefID nvarchar(100) AS
BEGIN

    INSERT INTO dbo.review(pID, email, review, refURL, refID)
    OUTPUT inserted.uID --This seems like an OUTPUT parameter might be better, as you insert a single row
    VALUES(@pID, @Email, @review, @RefURL, @RefID);
END;
GO

CREATE PROC dbo.Review_Update @id int, @pID int, @email nvarchar(255), @review nvarchar(3000), @RefURL nvarchar(2083), @RefID nvarchar(100) AS
BEGIN

    UPDATE dbo.review
    SET pID = @pID,
        email = @email,
        review = @review,
        refURL = @refURL,
        refID = @refID
    WHERE ID = @ID;
END;
GO

Notice I never declare the parameter @cDate, as you don't use it once in your SP.

If, for some really odd reason, you really need to have one SP, then create the others and call them dynamically; only passing the parameters you passed to the "master" SP to the "children". I, however, don't recommend this one and you should just call them correct one in the first place:

CREATE PROC Review_Operation @Operation char(6), --No need for this to be an nvarchar, or 50 characters, delete, insert and update are all 6 characters in length and contain no unicode characters
                             @ID int = NULL, @pID int = NULL, @email nvarchar(255) = NULL, @review nvarchar(3000) = NULL, @RefURL nvarchar(2083) = NULL, @RefID nvarchar(100) = NULL AS
BEGIN

    --Because they are all NULL we're going to use Dynamic SQKL to only pass parameters will a value to force the error
    DECLARE @SQL nvarchar(MAX),
            @Params nvarchar(MAX);

    IF @Operation = 'Delete' BEGIN

        SET @SQL = N'EXEC Review_Delete ' + CASE WHEN @ID IS NOT NULL THEN N'@id' ELSE N'' END + N';';
        SET @Params = N'@ID int';
        EXEC sp_executesql @SQL, @Params, @ID;

    END ELSE IF @Operation = 'Insert' BEGIN

        SET @SQL = N'EXEC Review_Insert ' + STUFF(CASE WHEN @pID IS NOT NULL THEN N',@pID = @pID' ELSE N'' END +
                                                  CASE WHEN @email IS NOT NULL THEN N',@email = @email' ELSE N'' END +
                                                  CASE WHEN @review IS NOT NULL THEN N',@review = @review' ELSE N'' END +
                                                  CASE WHEN @RefURL IS NOT NULL THEN N',@RefURL = @RefURL' ELSE N'' END +
                                                  CASE WHEN @RefID IS NOT NULL THEN N',@RefID = @RefID' ELSE N'' END,1,1,N'') + N';';
        SET @Params = N'@pID int, @email nvarchar(255), @review nvarchar(3000), @RefURL nvarchar(2083), @RefID nvarchar(100)';
        EXEC sp_executesql @SQL, @Params, @pID, @email, @review, @RefURL, @RefID;

    END ELSE IF @Operation = 'Update' BEGIN

        SET @SQL = N'EXEC Review_Update ' + STUFF(CASE WHEN @ID IS NOT NULL THEN N',@ID = @ID' ELSE N'' END +
                                                  CASE WHEN @pID IS NOT NULL THEN N',@pID = @pID' ELSE N'' END +
                                                  CASE WHEN @email IS NOT NULL THEN N',@email = @email' ELSE N'' END +
                                                  CASE WHEN @review IS NOT NULL THEN N',@review = @review' ELSE N'' END +
                                                  CASE WHEN @RefURL IS NOT NULL THEN N',@RefURL = @RefURL' ELSE N'' END +
                                                  CASE WHEN @RefID IS NOT NULL THEN N',@RefID = @RefID' ELSE N'' END,1,1,N'') + N';';
        SET @Params = N'@id int,@pID int, @email nvarchar(255), @review nvarchar(3000), @RefURL nvarchar(2083), @RefID nvarchar(100)';
        EXEC sp_executesql @SQL, @Params, @ID, @pID, @email, @review, @RefURL, @RefID;

    END;        

END;
GO