如何使用存储过程向多个客户添加多个位置?
问题描述:
我在如何使用存储过程向一个客户添加多个位置方面遇到问题。以下是我的表格,我正在使用索引来帮助我为一个客户添加多个位置,但是我应该如何为其添加多个位置?如何使用存储过程向多个客户添加多个位置?
CREATE TABLE TCustomer
(
intCustomerID INTEGER NOT NULL,
strCustomerName VARCHAR(50) NOT NULL,
strAddress VARCHAR(50) NOT NULL,
CONSTRAINT TCustomer_PK PRIMARY KEY (intCustomerID)
)
CREATE TABLE TCustomerLocation
(
intLocationID INTEGER NOT NULL,
intLocationIndex INTEGER NOT NULL,
intCustomerID INTEGER NOT NULL,
CONSTRAINT TCustomerLocation_PK
PRIMARY KEY(intLocationID, intLocationIndex)
)
CREATE TABLE TLocation
(
intLocationID INTEGER NOT NULL,
strLocationName VARCHAR(50) NOT NULL,
strLocationAddress VARCHAR(50) NOT NULL,
strLocationCity VARCHAR(50) NOT NULL,
CONSTRAINT TLocation_PK PRIMARY KEY(intLocationID)
)
CREATE PROCEDURE uspAddCustomerLocation
@strCustomerName VARCHAR(50),
@strAddress VARCHAR(50),
@strLocationName VARCHAR(50),
@strLocationAddress VARCHAR(50),
@strLocationCity VARCHAR(50)
AS
BEGIN TRANSACTION
SET XACT_ABORT ON
DECLARE @intCustomerID INTEGER
SELECT @intCustomerID = MAX(intCustomerID) + 1
FROM TCustomer (TABLOCKX) -- LOCK TABLE UNTIL THE END OF TRANSACTION
SELECT @intCustomerID = COALESCE(@intCustomerID , 1)
INSERT INTO TCustomer (intCustomerID, strCustomerName , strAddress)
VALUES(@intCustomerID ,@strCustomerName ,@strAddress)
DECLARE @intLocationID INTEGER
DECLARE @intLocationIndex INTEGER
SELECT @intLocationID = MAX(intLocationID) + 1
FROM TLocation (TABLOCKX) -- LOCK TABLE UNTIL THEEND OF TRANSACTION
COMMIT TRANSACTION
GO
答
我不是DBA,但我希望这些ID字段是IDENTITY字段。无论如何,我认为有问题的选择将是SELECT @intLocationIndex = MAX(intLocationIndex) +1 Where intCustomerID = @intCustomerID
程序代码是**高度供应商特定的** - 所以请添加一个标签来指定您是否使用'mysql','postgresql','sql-server' ,'oracle'或'db2' - 或者其他的东西。 –
我将从'TABLOCKX'提示中猜测它实际上是'sql-server' –