组内有一个牛逼的DBA,所以DB方面基本上都求助于它,最近发现如果用SQL过程,可以大节省code
USE [TestIT]
GO
/****** Object: StoredProcedure [dbo].[Study4] Script Date: 10/28/2015 15:02:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Study4]
AS
BEGIN
SET NOCOUNT ON;
Declare @markname varchar(50)
Declare @partnumber varchar(50)
Declare @bond varchar(5)
Declare @reqty int
Declare @id int
if exists (select * from MASTER.dbo.syscursors where cursor_name='markrecords') /*查找markrecords结果集,如果有就关闭*/
begin
close markrecords
deallocate markrecords
end
Declare markrecords cursor for select Req_Id,Market_Name,Part_Number,Nbonded,Req_Qty from Market_Record where Req_State=2 and Update_Flag=0
open markrecords /*创建markrecords结果集*/
FETCH NEXT FROM markrecords INTO @id ,@markname,@partnumber,@bond,@reqty /*第一次获取markrecords结果集记录,用于断定是否有数据*/
while @@FETCH_STATUS=0 /*循环markrecords结果集,直至结束*/
Begin
if exists (SELECT Id, Market_Name, Market_Loc, Part_Number, Nbonded, Min_Qty, Max_Qty, On_Hand FROM Market_List WHERE Market_Name = @markname AND Part_Number = @partnumber AND Nbonded = @bond)
begin
print cast(@id as varchar(15))+@markname +@partnumber+ @bond+cast(@reqty as varchar(5)) /*更新数据*/
UPDATE Market_List SET On_Hand =On_Hand+@reqty WHERE Market_Name=@markname and Part_Number=@partnumber and Nbonded=@bond /*更新数据*/
UPDATE Market_Record SET Update_Flag =1 WHERE Req_Id=@id
End
FETCH NEXT FROM markrecords INTO @id ,@markname,@partnumber,@bond,@reqty /*获取markrecords结果集记录*/
End
close markrecords
deallocate markrecords
END