another storeproc question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

another storeproc question

Author

Topic

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 11:11:57

[code]
--Creation of Procedure AddCustomer

create procedure AddCustomer

@Contact_Email Varchar(50),
@Contact_Name Varchar(30),
@Contact_Tel Varchar(20),
@S_Contact_Name Varchar(30),
@S_Contact_Tel Varchar(20),
@Forklift Varchar(4),
@Lift_Available Varchar(4),
@LoadingBay Varchar(4),
@Pallet_Storage Varchar(4),
@SiteAssistance Varchar(4),
@Vehicle_Access Varchar(4),
@StreetLoadRestrictions Varchar(50),
@Floor Varchar(20),
@Parking Varchar(4),
@Location_Name Varchar(40),
@Address_1 Varchar(40),
@Address_2 Varchar(40),
@Address_3 Varchar(40),
@Address_4 Varchar(40),
@Postcode Varchar(9),
@Company Varchar(30)

As

Begin

insert into Cust_Contact_tbl values (@Contact_Email,@Contact_Name,@Contact_Tel)
insert into Site_Contact_tbl values (@S_Contact_Name,@S_Contact_Tel)
insert into Site_Collection_tbl values (@Forklift,@Lift_Available,@LoadingBay,@Pallet_Storage,@SiteAssistance,
@Vehicle_Access,@StreetLoadRestrictions,@Floor,@Parking)
--The following is Line 36
insert into CustLocations_tbl values (@Location_Name,@Address_1,@Address_2,@Address_3,@Address_4,@Postcode)
insert into Customer_tbl values (@Company)

End [/code]

Msg 213, Level 16, State 1, Procedure AddCustomer, Line 36
Column name or number of supplied values does not match table definition.

the Custlocations table has 2 FKs which are Site_Contact_ID and Site_Collection_ID who's PK tables are Site_Contact_tbl and Site_Collection_tbl respectively.

I can see that the error is referring to an expected variable after postcode but I assumed that, as it's a FK it wouldn't need to be added in?

Can anyone give me some pointers to a solution?

Thanks

Paul

--
Paul

Complete SQL newbie!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-21 : 13:06:13

The error doesn't indicate any problem with foreign keys or any constraint violation. It seems that you didn't supply the correct number of values based on the table definition. You should explicitly list the insert column list to make less ambiguous. ie:

insert myTable (<columns to insert into>)
values (<values to insert>)

Keep in mind that you should exclude identity columns from both lists.

Be One with the Optimizer
TG

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-21 : 16:26:31

Thanks TG

I'll try this tomorrow at work :)

Paul

--
Paul

Complete SQL newbie!

G1ZmO
Starting Member

16 Posts

Posted - 2010-10-22 : 04:13:55

Thanks again TG (and Nic if you're reading this)

I changed the sp to this and it works fine :)


Begin

insert into Cust_Contact_tbl 
values (@Contact_Email,@Contact_Name,@Contact_Tel)

insert into Site_Contact_tbl 
values (@S_Contact_Name,@S_Contact_Tel)

insert into Site_Collection_tbl 
values (@Forklift,@Lift_Available,@LoadingBay,@Pallet_Storage,
@SiteAssistance,@Vehicle_Access,@StreetLoadRestrictions,@Floor,@Parking)

insert into CustLocations_tbl (Location_Name,Address_1,Address_2,Address_3,Address_4,Postcode)
values (@Location_Name,@Address_1,@Address_2,@Address_3,@Address_4,@Postcode)

insert into Customer_tbl (Company)
values (@Company)

End

--
Paul

Complete SQL newbie!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources