CODEWITHSUNDEEP
sqlstored-procedures
digrev
digrev

Reputation: 97

Confusion about Stored Procedure

I have written a stored procedure for inserting data into my table. These are my table's columns with their datatype:

Ad nvarchar(150),
Yazar nvarchar(150),
SayfaSayisi smallint,
KategoriId int
Gmc datetime,
HostName  nvarchar(150)

The problem is that Gmc and HostName have their own default values. So I can't use these two in the stored procedure.

Gmc ---> GetDate() (to get insert date)
HostName --> Host_Name(  )

So when I execute the query I am getting this error.

There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement

This is the query

Create proc Kitap_Insert
    @Ad nvarchar(150),
    @Yazar nvarchar(150),
    @SayfaSayisi smallint,
    @KategoriId int
    Gmc datetime,
    HostName nvarchar(150)
as
    Insert into Kitap(Id, Ad, Yazar, SayfaSayisi, KategoriId)
    values(@Ad, @Yazar, @SayfaSayisi, @KategoriId)

What is the proper way of doing this?

Upvotes: 0

Views: 72

Answers (3)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

The error you are getting because you tried to insert value into more column names than specified in Values Clause.

If you have ID column as Auto-increment field in table so you dont have to include that ID column in so your insert query will be like this:-

Insert into Kitap
           (Ad,Yazar,SayfaSayisi,KategoriId)
       values
           (@Ad,@Yazar,@SayfaSayisi,@KategoriId)

If you don't have ID column as Auto-increment field in table so you provide value to that id column also in Value Clause so your insert query will be like this:-

NOTE:- You have to calculate and Set Value to @Id variable before using it in Insert Query

  Declare @Id as INT
  SET @ID = ---- set here with some value which will become Primary key(I think)
  Insert into Kitap
           (Id,Ad,Yazar,SayfaSayisi,KategoriId)
       values
           (@Id, @Ad,@Yazar,@SayfaSayisi,@KategoriId)

Upvotes: 0

SMA
SMA

Reputation: 37023

Instead of :

Insert into Kitap(Id,Ad,Yazar,SayfaSayisi,KategoriId)
 values(@Ad,@Yazar,@SayfaSayisi,@KategoriId)

Use:

INSERT INTO Kitap(Ad,Yazar,SayfaSayisi,KategoriId)
VALUES (@Ad,@Yazar,@SayfaSayisi,@KategoriId)

You are asking SQL engine that you will provide id (an additional field) as well (field that doesn't exist in the table or is an auto increment field) and you are not providing the value for the same and hence your error here are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement

So remove additional Id from your insert query.

Upvotes: 0

Robert
Robert

Reputation: 25753

You need remove ID from insert list 

  Insert into Kitap(Ad,Yazar,SayfaSayisi,KategoriId)
     values(@Ad,@Yazar,@SayfaSayisi,@KategoriId)

or add a value for it as below

  Insert into Kitap(Id,Ad,Yazar,SayfaSayisi,KategoriId)
     values(@ID, @Ad,@Yazar,@SayfaSayisi,@KategoriId)

Upvotes: 1

Related Questions