Procedure expects parameter which was not supplied in SQL

1
votes

I'm getting this error when accessing a stored procedure in SQL Server:

Procedure or function 'resetdata' expects parameter '@FirstName', which was not supplied.

This is my table:

CREATE TABLE dbo.Client
(
    ClientID int IDENTITY(1,1) PRIMARY KEY NOT NULL, 
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    StreetAddress VARCHAR(50) NULL,
    Suburb VARCHAR(15)NULL,
    C_State VARCHAR(3) NULL, --CHECK (C_State IN ('QLD', 'NSW', 'VIC', 'TAS', 'SA', 'WA', 'NT', 'ACT')), 
    PostCode SMALLINT NOT NULL, 
    PhoneNumber VARCHAR(11) NULL, 
);
GO

ALTER TABLE Client
ADD CONSTRAINT state_ CHECK (C_State IN ('QLD', 'NSW', 'VIC', 'TAS', 'SA', 'WA', 'NT', 'ACT')),
    CONSTRAINT check_post_code CHECK ([PostCode] LIKE '[0-9][0-9][0-9][0-9]' or [PostCode] LIKE '[0-9][0-9][0-9]');
GO

This is my stored procedure:

CREATE PROCEDURE [dbo].[resetdata]
    @FirstName varchar(50),
    @LastName varchar(50),
    @PostCode smallint
AS
BEGIN
    INSERT INTO A1.dbo.Client (FirstName, LastName, PostCode)
    VALUES(@FirstName, @LastName, @PostCode)
END;
GO

EXEC dbo.resetdata;
INSERT INTO Client(FirstName, LastName, PostCode) 
VALUES ('F', 'L', 12345);
3
sql-serverstored-procedures
I'm curious as to the purpose of the procedure "dbo.resetdata". It does not reset anything; it just adds a new row. After calling the procedure, you insert a row manually, which suggests you were expecting it to do something else. Perhaps you could elaborate on what you are trying to accomplish. - yelxe

3 Answers

2
votes

Error message means exactly what it says: your SP is expecting three arguments as declared.

This is your SP header definition:

create procedure [dbo].[resetdata]
    @FirstName varchar(50),
    @LastName varchar(50),
    @PostCode smallint

Three arguments. None of them has default.

This is how you're attempting to execute it:

EXEC dbo.resetdata;

No arguments provided. This causes the error from your question. Pass arguments to avoid error message.

Couple of things:

  • if you will try to apply script of your SP to test database or another copy of your database, you will have to modify your first line with USE A1. I'd suggest to remove this line. Choose DB once you connect, then just run all scripts on current database
  • Insert INTO A1.dbo.Client - same note about different databases and one more - if you already defined database with USE A1 at the beginning of the script, why are you referring same DB explicitly? If your SP and table are supposed to be in same DB - don't specify DB.
0
votes

There are a couple of issues here:

  1. The EXEC procedure is not passing the parameters required.
  2. There is a constraint on the Postcode that will fail with 12345.

The correct code to run your stored procedure would be:

   EXEC [dbo].[resetdata]
        @FirstName = 'F',
        @LastName = 'L',
        @PostCode = 123
0
votes

Try this:

USE A1;
GO
create procedure [dbo].[resetdata]
@FirstName varchar(50),
@LastName varchar(50),
@PostCode smallint
AS
BEGIN
Insert INTO A1.dbo.Client (FirstName,LastName,PostCode)
VALUES(@FirstName,@LastName,@PostCode)
END;
GO

EXEC dbo.resetdata @FirstName='F', @LastName='L', @PostCode=12345
--INSERT INTO Client(FirstName,LastName,PostCode) VALUES ('F','L',12345); No need to this Insert

Note: In sample data, you are going to insert 12345 to PostCode column, So it seems that 5 digit numbers are valid, but you have check constraint that shows PostCode should be 3 or 4 digit length. If 5 digit is valid so pay attention to the PostCode column's data type as it's maximum value is 32767. So if you want to insert a record with PostCode greater than this value you will get an error. May be it would be better to define it as INT and control it's value via a Check Constraint and also change the current check constraint definition on this column.