如何设计这样的存储过程？

Question

存储过程可不可以在数据进行更新之前对将要更新的新数据和旧数据进行操作，比如说相加。
如果不行的话，如何设计这样一个存储过程，将某个Query数据集中的的数据写到一个表中去，当表中没有数据集的相应数据时，直接插入；当表中有数据集中的某列数据时，将表中此列的某个数据与数据集中此列的对应数据相加获得新的数据保存。

Answer 1

？？？？update table1 set field1=field1+1000 where field0=1

Answer 2

我自己设计了如下的存储过程
USE [DataBase]
GO
CREATE PROCEDURE [update_DRUG_STOCK]
(@STORAGE_1  [char],
 @DRUG_CODE_2  [char],
 @DRUG_SPEC_3  [char],
 @BATCH_NO_4  [char],
 @FIRM_ID_5  [char],
 @PACKAGE_SPEC_6  [char],
 @STORAGE_7  [char](8),
 @DRUG_CODE_8  [char](10),
 @DRUG_SPEC_9  [char](20),
 @UNITS_10  [char](8),
 @BATCH_NO_11  [char](16),
 @EXPIRE_DATE_12  [datetime],
 @FIRM_ID_13  [char](10),
 @PURCHASE_PRICE_14  [numeric],
 @DISCOUNT_15  [numeric],
 @PACKAGE_SPEC_16  [char](20),
 @QUANTITY_17  [numeric],
 @PACKAGE_UNITS_18  [char](8),
 @SUB_PACKAGE_19  [numeric],
 @SUB_PACKAGE_UNITS_20  [char](8),
 @SUB_PACKAGE_SPEC_21  [char](20),
 @SUB_STORAGE_22  [char](8),
 @LOCATION_23  [char](20),
 @DOCUMENT_NO_24  [char](10),
 @SUPPLY_INDICATOR_25  [tinyint])AS 
if exists(select * from DRUG_STOCK WHERE 
 [STORAGE]  = @STORAGE_1 AND
 [DRUG_CODE]  = @DRUG_CODE_2 AND
 [DRUG_SPEC]  = @DRUG_SPEC_3 AND
 [BATCH_NO]  = @BATCH_NO_4 AND
 [FIRM_ID]  = @FIRM_ID_5 AND
 [PACKAGE_SPEC]  = @PACKAGE_SPEC_6)UPDATE [HisDataBase].[dbo].[DRUG_STOCK] SET  [STORAGE]  = @STORAGE_7,
 [DRUG_CODE]  = @DRUG_CODE_8,
 [DRUG_SPEC]  = @DRUG_SPEC_9,
 [UNITS]  = @UNITS_10,
 [BATCH_NO]  = @BATCH_NO_11,
 [EXPIRE_DATE]  = @EXPIRE_DATE_12,
 [FIRM_ID]  = @FIRM_ID_13,
 [PURCHASE_PRICE]  = @PURCHASE_PRICE_14,
 [DISCOUNT]  = @DISCOUNT_15,
 [PACKAGE_SPEC]  = @PACKAGE_SPEC_16,
 [QUANTITY]  = @QUANTITY_17,
 [PACKAGE_UNITS]  = @PACKAGE_UNITS_18,
 [SUB_PACKAGE]  = @SUB_PACKAGE_19,
 [SUB_PACKAGE_UNITS]  = @SUB_PACKAGE_UNITS_20,
 [SUB_PACKAGE_SPEC]  = @SUB_PACKAGE_SPEC_21,
 [SUB_STORAGE]  = @SUB_STORAGE_22,
 [LOCATION]  = @LOCATION_23,
 [DOCUMENT_NO]  = @DOCUMENT_NO_24,
 [SUPPLY_INDICATOR]  = @SUPPLY_INDICATOR_25 WHERE 
( [STORAGE]  = @STORAGE_1 AND
 [DRUG_CODE]  = @DRUG_CODE_2 AND
 [DRUG_SPEC]  = @DRUG_SPEC_3 AND
 [BATCH_NO]  = @BATCH_NO_4 AND
 [FIRM_ID]  = @FIRM_ID_5 AND
 [PACKAGE_SPEC]  = @PACKAGE_SPEC_6)elseINSERT INTO [HisDataBase].[dbo].[DRUG_STOCK] 
 ( [STORAGE],
 [DRUG_CODE],
 [DRUG_SPEC],
 [UNITS],
 [BATCH_NO],
 [EXPIRE_DATE],
 [FIRM_ID],
 [PURCHASE_PRICE],
 [DISCOUNT],
 [PACKAGE_SPEC],
 [QUANTITY],
 [PACKAGE_UNITS],
 [SUB_PACKAGE],
 [SUB_PACKAGE_UNITS],
 [SUB_PACKAGE_SPEC],
 [SUB_STORAGE],
 [LOCATION],
 [DOCUMENT_NO],
 [SUPPLY_INDICATOR]) 
 
VALUES 
( @STORAGE_7,
 @DRUG_CODE_8,
 @DRUG_SPEC_9,
 @UNITS_10,
 @BATCH_NO_11,
 @EXPIRE_DATE_12,
 @FIRM_ID_13,
 @PURCHASE_PRICE_14,
 @DISCOUNT_15,
 @PACKAGE_SPEC_16,
 @QUANTITY_17,
 @PACKAGE_UNITS_18,
 @SUB_PACKAGE_19,
 @SUB_PACKAGE_UNITS_20,
 @SUB_PACKAGE_SPEC_21,
 @SUB_STORAGE_22,
 @LOCATION_23,
 @DOCUMENT_NO_24,
 @SUPPLY_INDICATOR_25)
不知道有没更好的解决办法

Answer 3

CREATE PROCEDURE [update_DRUG_STOCK]
(@STORAGE_1  [char],
 @DRUG_CODE_2  [char],
 @DRUG_SPEC_3  [char],
 @BATCH_NO_4  [char],
 @FIRM_ID_5  [char],
 @PACKAGE_SPEC_6  [char],
 @STORAGE_7  [char](8),
 @DRUG_CODE_8  [char](10),
 @DRUG_SPEC_9  [char](20),
 @UNITS_10  [char](8),
 @BATCH_NO_11  [char](16),
 @EXPIRE_DATE_12  [datetime],
 @FIRM_ID_13  [char](10),
 @PURCHASE_PRICE_14  [numeric],
 @DISCOUNT_15  [numeric],
 @PACKAGE_SPEC_16  [char](20),
 @QUANTITY_17  [numeric],
 @PACKAGE_UNITS_18  [char](8),
 @SUB_PACKAGE_19  [numeric],
 @SUB_PACKAGE_UNITS_20  [char](8),
 @SUB_PACKAGE_SPEC_21  [char](20),
 @SUB_STORAGE_22  [char](8),
 @LOCATION_23  [char](20),
 @DOCUMENT_NO_24  [char](10),
 @SUPPLY_INDICATOR_25  [tinyint])AS 
if exists(select * from DRUG_STOCK WHERE 
 [STORAGE]  = @STORAGE_1 AND
 [DRUG_CODE]  = @DRUG_CODE_2 AND
 [DRUG_SPEC]  = @DRUG_SPEC_3 AND
 [BATCH_NO]  = @BATCH_NO_4 AND
 [FIRM_ID]  = @FIRM_ID_5 AND
 [PACKAGE_SPEC]  = @PACKAGE_SPEC_6)UPDATE [HisDataBase].[dbo].[DRUG_STOCK] SET  [STORAGE]  = @STORAGE_7,
 [DRUG_CODE]  = @DRUG_CODE_8,
 [DRUG_SPEC]  = @DRUG_SPEC_9,
 [UNITS]  = @UNITS_10,
 [BATCH_NO]  = @BATCH_NO_11,
 [EXPIRE_DATE]  = @EXPIRE_DATE_12,
 [FIRM_ID]  = @FIRM_ID_13,
 [PURCHASE_PRICE]  = @PURCHASE_PRICE_14,
 [DISCOUNT]  = @DISCOUNT_15,
 [PACKAGE_SPEC]  = @PACKAGE_SPEC_16,
 [QUANTITY]  =[QUANTITY]+ @QUANTITY_17,
 [PACKAGE_UNITS]  = @PACKAGE_UNITS_18,
 [SUB_PACKAGE]  = @SUB_PACKAGE_19,
 [SUB_PACKAGE_UNITS]  = @SUB_PACKAGE_UNITS_20,
 [SUB_PACKAGE_SPEC]  = @SUB_PACKAGE_SPEC_21,
 [SUB_STORAGE]  = @SUB_STORAGE_22,
 [LOCATION]  = @LOCATION_23,
 [DOCUMENT_NO]  = @DOCUMENT_NO_24,
 [SUPPLY_INDICATOR]  = @SUPPLY_INDICATOR_25 WHERE 
( [STORAGE]  = @STORAGE_1 AND
 [DRUG_CODE]  = @DRUG_CODE_2 AND
 [DRUG_SPEC]  = @DRUG_SPEC_3 AND
 [BATCH_NO]  = @BATCH_NO_4 AND
 [FIRM_ID]  = @FIRM_ID_5 AND
 [PACKAGE_SPEC]  = @PACKAGE_SPEC_6)elseINSERT INTO [HisDataBase].[dbo].[DRUG_STOCK] 
 ( [STORAGE],
 [DRUG_CODE],
 [DRUG_SPEC],
 [UNITS],
 [BATCH_NO],
 [EXPIRE_DATE],
 [FIRM_ID],
 [PURCHASE_PRICE],
 [DISCOUNT],
 [PACKAGE_SPEC],
 [QUANTITY],
 [PACKAGE_UNITS],
 [SUB_PACKAGE],
 [SUB_PACKAGE_UNITS],
 [SUB_PACKAGE_SPEC],
 [SUB_STORAGE],
 [LOCATION],
 [DOCUMENT_NO],
 [SUPPLY_INDICATOR]) 
 
VALUES 
( @STORAGE_7,
 @DRUG_CODE_8,
 @DRUG_SPEC_9,
 @UNITS_10,
 @BATCH_NO_11,
 @EXPIRE_DATE_12,
 @FIRM_ID_13,
 @PURCHASE_PRICE_14,
 @DISCOUNT_15,
 @PACKAGE_SPEC_16,
 @QUANTITY_17,
 @PACKAGE_UNITS_18,
 @SUB_PACKAGE_19,
 @SUB_PACKAGE_UNITS_20,
 @SUB_PACKAGE_SPEC_21,
 @SUB_STORAGE_22,
 @LOCATION_23,
 @DOCUMENT_NO_24,
 @SUPPLY_INDICATOR_25)
GO
开始发错了
就这个地方不一样
 [QUANTITY]  =[QUANTITY]+ @QUANTITY_17,