一个有用的存储过程（原创）

/*Add SupplierDish*/
CREATE PROCEDURE SupplierDishAdd
@SupplierID int,
@CuisineID int,
@DishID int,
@DishNo varchar(20),
@SupplierDishName varchar(50),
@Price smallmoney,
@DishDescription text,
@Recipe text,
@StockLevel int,
@ReligionIDStr varchar(1000),
@IsCopyDishOptionGroup int,
@Online int,
@SpecialOfferNo int,
@ResultCode int output
 AS
Begin TransAction

declare @i int,@NewOptionGroupID int,@SupplierDishID int

/*insert data to table supplierdish*/
Insert Into SupplierDish(SupplierID,DishID,DishNo,SupplierDishName,Price,DishDescription,Recipe,StockLevel,Online,SpecialOfferNo)
            Values(@SupplierID,@DishID,@DishNo,@SupplierDishName,@Price,@DishDescription,@Recipe,@StockLevel,@Online,@SpecialOfferNo)
 Select @SupplierDishID=@@Identity

If @@Error<>0 GoTo DBRollBack

/*Begin   添加SupplierDish的Religion*/
  Insert Into SupplierDishReligion  Select * From dbo.SplitOptionID(@ReligionIDStr,@SupplierDishID,',')
/*End    添加SupplierDish的Religion*/

If @@Error<>0 GoTo DBRollBack
--判断是否copy DishOptionGroup
If(@IsCopyDishOptionGroup=1)
Begin
/*Begin   循环Copy Dish的OptionGroup成为SupplierDish的OptionGroup*/

Select identity(int,1,1) as id,OptionGroupID into #t From DishCustomization Where DishID=@DishID
select @i=isnull(max(id),0) from #t
while @i>0
 begin
  --copy OptionGroup
  insert into OptionGroup (OptionGroupTitle,[Description],IsMultiple) select OptionGroupTitle,[Description],IsMultiple from OptionGroup where OptionGroupID in (select OptionGroupID from #t where id=@i)
  select @NewOptionGroupID=@@identity
  --copy CustomizationOption
  insert into CustomizationOption (OptionGroupID,OptionTitle) select OptionGroupID,OptionTitle from CustomizationOption where OptionGroupID in (select OptionGroupID from #t where id=@i)
  --insert date to Table(SupplierDishCustomization)
  Insert Into SupplierDishCustomization(SupplierDishID,OptionGroupID) Values(@SupplierDishID,@NewOptionGroupID)
  select @i=@i-1
 end

/*End  循环Copy Dish的OptionGroup成为SupplierDish的OptionGroup*/
If @@Error<>0 GoTo DBRollBack

End

/*insert data to table suppliercuisine*/
if(not exists(Select * From SupplierCuisine Where SupplierID=@SupplierID And CuisineID=@CuisineID))
   Insert Into SupplierCuisine(SupplierID,CuisineID) values(@SupplierID,@CuisineID)

If @@Error<>0 GoTo DBRollBack
Begin
Commit TransAction
Select @ResultCode=0
End

return

/*if somewhere error,rollback*/
DBRollBack:

Begin

RollBack TransAction
Select @ResultCode=1

End
return

GO