SQL Server stored procedure expects parameter which was not supplied

2
votes
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'

5
sql-servertsqlstored-proceduressql-server-2012
The procedure expects you to give it a parameter called uID when you execute it. You didn't do that. I don't think the error could really be much clearer?? - ADyson
This is always a problem when you have these procedures that do a gazillion different things at once. This might look like a bright idea at first - but it's a horrible design, and it's contrary to the single responsibility principle - don't do this. You should have three separate, specific procedures - one for each operation. That's the much better, much more readable and easier to maintain long-term approach. - marc_s
You gave it one called id, and one called OPERATION, but there are several other required parameters, including uiD, which you haven't provided - ADyson
But yes, life would be simpler if you just have separate procedures for each operation. It would be easier to validate the data, easier to test, easier to maintain, and easier to call them correctly - ADyson
Yes, if you give them default values (even if the default is null). But then it's harder to validate the input. Just have 3 separate procedures. We keep saying it - ADyson

5 Answers

2
votes

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

This means you are not passing a value to @uID when you are calling the stored procedure. You need to pass a valid parameter during SP execution.

2
votes

You have couple of issues with your procedure creation and calling.

Creation: You declared a parameter @uID VARCHAR(6) with no use in the Procedure. You can remove the line @uID VARCHAR(6) from the procedur's parameter definition section.

Calling: you defined parameter - @email, @review, @refURL, @refID in the procedure but not providing values to them while calling the procedure. You should call the procedure as below-

DECLARE @return_value int

EXEC    @return_value = [dbo].[spReview]
        @id = N'29',
        @email = N'[email protected]',
        @OPERATION = N'Delete',
        @email = '[email protected]', 
        @review = 'abc', 
        @refURL = 'xyz', 
        @refID = '1' -- ID should be a INT but declared as NVARCHAR

SELECT  'Return Value' = @return_value

GO
2
votes

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.

2
votes

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
-1
votes

Though not really recommended as stated above, you can provide default values, and in fact you have default values set for @id, @pID, and @OPERATION.

Of course, if you default @uID to null, you MUST account for that in the actual procedure to make sure a null @uID won't cause other errors.

create proc [dbo].[spReview] 
    @id INT = 0,
    @uID VARCHAR(6) = null,
    @pID INT = 0,
    @email NVARCHAR(255),
    @review NVARCHAR(3000),
    @refURL NVARCHAR(2083),
    @refID NVARCHAR(100),
    @cDate DATETME = NULL,
    @OPERATION NVARCHAR(50) = ''
AS
if @uID is null
   begin
     -- do something here when @uID is null.
   end