|
Subject:
|
Stored Procedure Query
|
|
Posted By:
|
anilmohakar
|
Post Date:
|
11/11/2005 2:14:58 AM
|
Hi,
I am facing one problem in stored procedure.I am using Professional sql server 2000 programming book.I am trying to execute one stored procedure , but it is giving error.
the procedure is - i am inserting one record into the orders table of northwind database.So OUTPUT keyword is there.When I click the check systax button on the window of stored procedure in enterprise manager,at that time it is generating an error message.the message is - Error 213 :Insert error:Column name or number of supplied values does not match table definition.
So what is that error.I am attaching the code.
USE Northwind
GO
CREATE PROC spInsertOrder
@CustomerID nvarchar(5),
@EmployeeID int,
@OrderDate smalldatetime=NULL,
@RequiredDate smalldatetime=NULL,
@ShippedDate smalldatetime=NULL,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40)=NULL,
@ShipAddress nvarchar(60)=NULL,
@ShipCity nvarchar(15)=NULL,
@ShipRegion nvarchar(15)=NULL,
@ShipPostalCode nvarchar(10)=NULL,
@ShipCountry nvarchar(15)=NULL,
@OrderID int OUTPUT
AS
/* Create the new record */
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@OrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
/* Move the identity value from the newly inserted record into
our output variable */
SELECT @OrderID = @@IDENTITY
So please send the solution
Anil
|
|
Reply By:
|
robprell
|
Reply Date:
|
11/11/2005 11:31:01 PM
|
----------------------------------------------------------------------------------------------------
-- Create the table (if your not using northwind database)
----------------------------------------------------------------------------------------------------
-- drop table Orders
create table Orders ( Orderid int identity,
CustomerID nvarchar(5),
EmployeeID int null,
OrderDate smalldatetime NULL,
RequiredDate smalldatetime NULL,
ShippedDate smalldatetime NULL,
ShipVia int null,
Freight money null,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL,
)
GO
----------------------------------------------------------------------------------------------------
-- Create the proc
----------------------------------------------------------------------------------------------------
-- drop procedure spInsertOrder
CREATE PROC spInsertOrder
@CustomerID nvarchar(5),
@EmployeeID int,
@OrderDate smalldatetime=NULL,
@RequiredDate smalldatetime=NULL,
@ShippedDate smalldatetime=NULL,
@ShipVia int,
@Freight money,
@ShipName nvarchar(40)=NULL,
@ShipAddress nvarchar(60)=NULL,
@ShipCity nvarchar(15)=NULL,
@ShipRegion nvarchar(15)=NULL,
@ShipPostalCode nvarchar(10)=NULL,
@ShipCountry nvarchar(15)=NULL,
@OrderID int OUTPUT
AS
/* Create the new record */
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@OrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
/* Move the identity value from the newly inserted record into
our output variable */
SELECT @OrderID = @@IDENTITY
return @OrderID
go
----------------------------------------------------------------------------------------------------
-- Insert a row and return the rowid (run this mutiple times, watch the count move)
----------------------------------------------------------------------------------------------------
declare @orderid int
exec spInsertOrder 'abc',123,'10/10/2005','10/10/2005','10/10/2005',1
,123.45,'shipname','addy','city','region','postal','nowhere',@Orderid out
select @orderid
-----------------------------------------------------------------------------------------------------
select * from Orders
|
|
