作用:检查xx与应用表的重复性，同时向两个表中同时插入数据>>新增/删除
Author:xx
Finish DateTime: 2011-3-15 11:31:13
Return Value:
-1:存储过程执行失败
-2:存在相同的主键
Insert:返回插入自动ID
Update:返回更新记录数
Delete:返回删除记录数
*********************************************************************************/
ALTER PROCEDURE [dbo].[P_Res_Car_InsertDelete]
(
@Erp_CarID int = 0, -- Erp_CarID
@P_CarID int = 0, -- 租车ID
@CarName nvarchar(50) = '', -- 车型名
@CarCardID int = 0, -- 车品牌ID
@BoxAmount nvarchar(50) = '', -- 厢式/车体
@SeatAmount nvarchar(50) = '', -- 座位数
@Shelves nvarchar(50) = '', -- 变速箱
@Quantity nvarchar(50) = '', -- 排量
@OilUse nvarchar(50) = '', -- 油耗
@OilSandard nvarchar(50) = '', -- 燃油标准
@CarIntroduce nvarchar(1000) = '', -- 车型介绍
@Adding bit = 0, -- 追加标识- 云计算默认为true ，应用平台为false
@FillName nvarchar(50) = '', -- 发布人
@FillDateTime datetime = getdate, -- 发布时间
@DataTable_Action_ varchar(10) = '' -- 操作方法 Insert:增加 Update:修改 Delete:删除
)
AS
DECLARE @ReturnValue varchar(18) -- 返回操作结果
DECLARE @P_Cid int = 88 -- 提交云计算表返回的id
SET @ReturnValue = -1
-- 新增
IF (@DataTable_Action_='Insert')
begin
if exists(select * from P_Car where CarName=@CarName and BoxAmount=@BoxAmount and Shelves=@Shelves and Quantity=@Quantity )
begin
--云计算平台已存在相同记录
SET @ReturnValue = -9
END
else if exists(select * from Res_Car where CarName=@CarName and BoxAmount=@BoxAmount and Shelves=@Shelves and Quantity=@Quantity )
begin
--应用平台已存在相同记录
SET @ReturnValue = -99
END
else
BEGIN
begin tran --开始事务
--提交云计算
INSERT INTO P_Car(CarName,CarCardID,BoxAmount,SeatAmount,Shelves,Quantity,OilUse,OilSandard,CarIntroduce,Auditing,FillName,FillDateTime,ERPState
) VALUES (@CarName,@CarCardID,@BoxAmount,@SeatAmount,@Shelves,@Quantity,@OilUse,@OilSandard,@CarIntroduce,'0',@FillName,@FillDateTime ,'1')
if @@error<>0
begin
rollback tran --回滚事务
SET @ReturnValue = -1
end
SELECT @P_Cid = SCOPE_IDENTITY()
--提交应用平台
INSERT INTO Res_Car(P_CarID,CarName,CarCardID,BoxAmount,SeatAmount,Shelves,Quantity,OilUse,OilSandard,CarIntroduce,Adding,FillName,FillDateTime
) VALUES (@P_Cid,@CarName,@CarCardID,@BoxAmount,@SeatAmount,@Shelves,@Quantity,@OilUse,@OilSandard,@CarIntroduce,@Adding,@FillName,@FillDateTime)
if @@error<>0
begin
rollback tran --回滚事务
SET @ReturnValue = -1
end
commit tran --提交事务
SELECT @ReturnValue = SCOPE_IDENTITY()
end
end
--删除
IF (@DataTable_Action_='Delete')
BEGIN
DELETE Res_Car WHERE (Erp_CarID=@Erp_CarID)
SET @ReturnValue = @@ROWCOUNT
END
SELECT @ReturnValue
