--表結構
USE [OnlyTest]
GO
/****** Object: Table [dbo].[Stock] Script Date: 11/30/2012 18:10:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stock]') AND type in (N'U'))
DROP TABLE [dbo].[Stock]
GO
USE [OnlyTest]
GO
/****** Object: Table [dbo].[Stock] Script Date: 11/30/2012 18:10:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stock](
[StockID] [bigint] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[StockDate] [date] NOT NULL,
[StockQty] [int] NOT NULL,
CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED
(
[ProductID] ASC,
[StockDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [OnlyTest]
GO
/****** Object: StoredProcedure [dbo].[usp_OptStock] Script Date: 11/30/2012 18:09:35 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_OptStock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_OptStock]
GO
/****** Object: StoredProcedure [dbo].[usp_OptStock_SmallCode] Script Date: 11/30/2012 18:09:35 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_OptStock_SmallCode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_OptStock_SmallCode]
GO
USE [OnlyTest]
GO
/****** Object: StoredProcedure [dbo].[usp_OptStock] Script Date: 11/30/2012 18:09:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--並發庫存，標準做法
CREATE PROCEDURE [dbo].[usp_OptStock]
@ProductID INT ,
@StartDate DATE ,
@EndDate DATE ,
@OptNumber INT
AS
DECLARE @OptDays INT ;
DECLARE @HaveDays INT ;
SET @OptDays = DATEDIFF(day, @StartDate, @EndDate) + 1 ;
BEGIN TRANSACTION ;
SELECT @HaveDays = COUNT(*)
FROM dbo.Stock
WHERE StockQty >= @OptNumber
AND StockDate >= @StartDate
AND StockDate <= @EndDate
AND ProductID = @ProductID
IF @HaveDays = @OptDays
BEGIN
UPDATE dbo.Stock
SET StockQty = StockQty - @OptNumber
WHERE StockQty >= @OptNumber
AND StockDate >= @StartDate
AND StockDate <= @EndDate
AND ProductID = @ProductID
COMMIT ;
RETURN 0 ;
END
ELSE
BEGIN
ROLLBACK ;
RETURN -110 ;
END
GO
/****** Object: StoredProcedure [dbo].[usp_OptStock_SmallCode] Script Date: 11/30/2012 18:09:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--並發庫存，魯莽做法。
CREATE PROCEDURE [dbo].[usp_OptStock_SmallCode]
@ProductID INT ,
@StartDate DATE ,
@EndDate DATE ,
@OptNumber INT
AS
DECLARE @OptDays INT ;
SET @OptDays = DATEDIFF(day, @StartDate, @EndDate) + 1 ;
BEGIN TRANSACTION
UPDATE dbo.Stock
SET StockQty = StockQty - @OptNumber
WHERE StockQty >= @OptNumber
AND StockDate >= @StartDate
AND StockDate <= @EndDate
AND ProductID = @ProductID
IF @@ROWCOUNT = @OptDays
BEGIN
COMMIT
RETURN 0
END
ELSE
BEGIN
ROLLBACK
RETURN -110
END
GO
--測試代碼
USE OnlyTest
GO
TRUNCATE TABLE dbo.Stock
INSERT INTO dbo.Stock VALUES
( 1,'2012-1-1',1),
( 1,'2012-1-2',2),
( 1,'2012-1-3',3),
( 1,'2012-1-4',4),
( 1,'2012-1-5',5)
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_OptStock]
@ProductID = 1,
@StartDate = '2012-1-2',
@EndDate = '2012-1-4',
@OptNumber = 2
SELECT '庫存充足返回' = @return_value
SELECT * FROM dbo.Stock
EXEC @return_value = [dbo].[usp_OptStock]
@ProductID = 1,
@StartDate = '2012-1-2',
@EndDate = '2012-1-4',
@OptNumber = 2
SELECT '庫存不足返回' = @return_value
SELECT * FROM dbo.Stock |
