存储过程的定义
CREATE procedure pro_buyGoods
(
@GoodsName varchar()
@GoodsNum int
)
AS
declare @count as int
set @count = (select count(*) from T_Goods where GoodsName=@GoodsName)
if @count<= begin
insert into T_Goods values (@GoodsName@GoodsNum)
end
else begin
update T_Goods set GoodsNum=GoodsNum+@GoodsNum where GoodsName=@GoodsName
end
GO
说明语法是通过检查的这个过程是用来判断T_Goods表中有没有指定的商品有的话则增加他的数目没有的话新添加记录
这是在ASPNET中的调用过程
int BuyGoodsID = ConvertToInt(thistxtBuyIDText);
string GoodsName = thistxtGoodsNameText;
int GoodsNum = ConvertToInt(thistxtGoodsNumText);
int GoodsPrice = ConvertToInt(thistxtGoodsPriceText);
SqlConnection conn = DBCreateCon();
connOpen();
string cmdText = insert into T_BuyGoods values(+BuyGoodsID++GoodsName++GoodsNum++GoodsPrice+);
SqlCommand cmd = new SqlCommand(cmdTextconn);
SqlTransaction trans = connBeginTransaction();
cmdTransaction = trans;
try
{
cmdExecuteNonQuery();
//插入库存表的代码
SqlCommand cmd = new SqlCommand(pro_buyGoodsconn);
cmdCommandType = CommandTypeStoredProcedure;
cmdParametersAdd(@GoodsNameSqlDbTypeVarChar);
cmdParametersAdd(@GoodsNumSqlDbTypeInt);
cmdParameters[@GoodsName]Direction=ParameterDirectionInput;
cmdParameters[@GoodsNum]Direction=ParameterDirectionInput;
cmdParameters[@GoodsName]Value = GoodsName;
cmdParameters[@GoodsNum]Value = GoodsNum;
cmdExecuteNonQuery();
thisPanelVisible = false;
thisPanelVisible = true;
thislblInfoText = 提交成功！;
transCommit();
}
catch(Exception exp)
{
transRollback();
ResponseWrite(<script language=javascript>alert(提交失败需要重新提交！)<+/script>);
}
finally
{
connClose();
}
***********************************************************************
set @count = (select count(*) from T_Goods where GoodsName=@GoodsName)
改为
select @count = count(*) from T_Goods where GoodsName=@GoodsName
