ALTER PROCEDURE [dbo].[AddEquipmentApplyDetails]
( @EADEaID int = null,
@EADVbiCode varchar(50) = null,
@EADEbiCode varchar(50) = null,
@EADSpecification int = null,
@EADUnit int = null,
@DADNum int = null,
@DADDesc varchar(200) = null,
@EADFillTime datetime = null,
@EADFillPerson varchar(20) = null,
@EADUpdateTime datetime = null,
@EADUpdatePerson varchar(20) = null,
@EBIName varchar(50) = null
)
AS
BEGINset xact_abort on
BEGIN TRAN
DECLARE @EBICode varchar(50)
SET @EBICode =(SELECT EBICode FROM EquipmentBaseInfo WHERE EquipmentBaseInfo.EBIName= @EBIName)
INSERT INTO [VHC_WareHouse].[dbo].[EquipmentApplyDetails]
VALUES
(
@EADEaID ,
@EADVbiCode,
@EBICode,
@EADSpecification,
@EADUnit,
@DADNum,
@DADDesc,
@EADFillTime,
@EADFillPerson,
@EADUpdateTime,
@EADUpdatePerson)
commit tran
END大概意思就是从EquipmentBaseInfo查出EBICode赋值给EquipmentApplyDetails的EADEbiCode,但插入的时候,我用的是变量@EBICode ,谢谢大家啦
解决方案 »
- SQL server 2000
- 聚合更新的问题
- Sql server 与web页交互
- SQL 插入图片 高分求
- 任何在SQL脚本中执行另一个SQL脚本?
- 向sql2005导入excel的时候,居然不能连接源文件?!
- SQL存储过程语句太长,请问应该怎样优化?
- 如何动态的统计一张表的数据到另一张表,被统计的表是随时更新的问题!十万火急,,,,已经研究好几天了,但是因缺乏经验,一直无法解决
- ****高分求助!!!!我现在在.net环境下编程,语言用vb,请问有没有函数可以实现:把用逗号分割的字符串转成字符串数组,还有它的逆过程
- sql 2012怎么定义某一列update的触发器?书上说用 of ,运行的时候不对啊
- 关于表触发器?(100分+100%结贴率)
- 如何将一个表的自增字段重新从1开始(50分)?
--当取的不是一条记录是会有问题
SET @EBICode =(SELECT top 1 EBICode FROM EquipmentBaseInfo WHERE EquipmentBaseInfo.EBIName= @EBIName)
--还有[EquipmentApplyDetails]列建议写全,因为如果后面的值传的和列不等(假设无自增列),则有错
--以后你如果改了表EquipmentApplyDetails,新增了一列,则你的此存储过程必须改,这种设计
--是不合理的,因此建议把列写了
--那你必要另加个变量的,直接这样写可以
--建议你还是把[VHC_WareHouse].[dbo].[EquipmentApplyDetails](列1,列2)这些加进来
--等哪天你给表EquipmentApplyDetails加个列,你就知道加进来的好处了,等你数据库中有上千个像类似不加列的SP,如果改一个表,这些SP会改的很头大的,呵呵
insert into [VHC_WareHouse].[dbo].[EquipmentApplyDetails]
select top 1 @EADEaID ,
@EADVbiCode,
EBICode,
@EADSpecification,
@EADUnit,
@DADNum,
@DADDesc,
@EADFillTime,
@EADFillPerson,
@EADUpdateTime,
@EADUpdatePerson
from EquipmentBaseInfo
where EquipmentBaseInfo.EBIName= @EBIName;
ALTER PROCEDURE [dbo].[AddEquipmentApplyDetails]
( @EADEaID int = null,
@EADVbiCode varchar(50) = null,
@EADEbiCode varchar(50) = null,
@EADSpecification int = null,
@EADUnit int = null,
@DADNum int = null,
@DADDesc varchar(200) = null,
@EADFillTime datetime = null,
@EADFillPerson varchar(20) = null,
@EADUpdateTime datetime = null,
@EADUpdatePerson varchar(20) = null,
@EBIName varchar(50) = null
)
AS
BEGINset xact_abort on
BEGIN TRAN
INSERT INTO
[VHC_WareHouse].[dbo].[EquipmentApplyDetails]
select
@EADEaID ,
@EADVbiCode,
EBICode ,
@EADSpecification,
@EADUnit,
@DADNum,
@DADDesc,
@EADFillTime,
@EADFillPerson,
@EADUpdateTime,
@EADUpdatePerson
FROM
EquipmentBaseInfo WHERE EquipmentBaseInfo.EBIName= @EBIName
commit tran
END--最好给出测试数据
自增主键是不用插入的,我是说其他列,给你举个例子吧,别说我以后误导你了alter table EquipmentApplyDetails add appendCol varchar(20) null;
--这时你再试你的存储过程,难道不会出错吗
--可能你不太用到而已,如果一个系统1000多张表,几千个像你这样的存储过程,人家会改死的,呵呵