CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Pluto
Pluto

Reputation: 859

Insert new data to a table with stored procedure

I tried to make a stored procedure the insert data to a table:

create procedure AddEmployee
(
    @FirstName nvarchar(20)
    , @LastName nvarchar(20)
    , @BirthDate datetime
    , @Country nvarchar(15)
    , @City nvarchar(15)
)
as
    insert into Employees
    values (@FirstName, @LastName, @BirthDate, @Country, @City)
go

But when I run it I get the error message:

Msg 213, Level 16, State 1, Procedure AddEmployee, Line 2 [Batch Start Line 17]
Column name or number of supplied values does not match table definition.

I looked at this question but it didn't solve my problem: Create a stored procedure to insert new data into a table

Upvotes: 0

Views: 1547

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

When using insert, always include the columns names:

create procedure AddEmployee (
    @FirstName nvarchar(20) ,
    @LastName nvarchar(20) ,
    @BirthDate datetime,
    @Country nvarchar(15),
    @City nvarchar(15)
) as
begin
    insert into Employees (FirstName, LastName, BirthDate, Country, City)
        values (@FirstName, @LastName, @BirthDate, @Country, @City);
end;

Although SQL allows you to leave out the column names, you should include them as a best-practice. This is particularly important for those learning the language, so they learn the safer way to do things.

Upvotes: 3

Related Questions