产品构建表的添加存储过程：
CREATE PROCEDURE [dbo].[BioPurchaseAppInfo_ADD]
@PurchaseID INT OUTPUT,
@Subject NVARCHAR(100),
@DepartMentID INT,
@AppUserId INT,
@RealUserID INT,
@OrderCom NVARCHAR(50),
@SendComID INT,
@OrderTime DATETIME,
@sendType NVARCHAR(50),
@WholeType NVARCHAR(30),
@AcceptUserid INT,
@BillUserID INT,
@BillNo NVARCHAR(30),
@DeleteSate BIT AS
INSERT INTO [BioPurchaseAppInfo](
[Subject],[DepartMentID],[AppUserId],[RealUserID],[OrderCom],[SendComID],[OrderTime],[sendType],[WholeType],
[AcceptUserid],[BillUserID],[BillNo],[DeleteSate]
)VALUES(
@Subject,@DepartMentID,@AppUserId,@RealUserID,@OrderCom,@SendComID,@OrderTime,@sendType,@WholeType,@AcceptUserid,@BillUserID,@BillNo,@DeleteSate
)
SET @PurchaseID = @@IDENTITY
修改的存储过程：
CREATE PROCEDURE [dbo].[BioPurchasePro_Update]
@PurchaseProID INT,
@PurchaseID INT,
@ProID INT,
@ProCount INT,
@ProPrice MONEY,
@isInvoice BIT,
@isPay BIT,
@InvoicePrice MONEY
AS
UPDATE [BioPurchasePro] SET
[PurchaseID] = @PurchaseID,[ProID] = @ProID,[ProCount] = @ProCount,[ProPrice] = @ProPrice,[isInvoice] = @isInvoice,[isPay] = @isPay,[InvoicePrice] = @InvoicePrice
WHERE PurchaseProID=@PurchaseProID
添加的存储过程：
CREATE PROCEDURE [dbo].[BioPurchasePro_ADD]
@PurchaseID INT,
@ProID INT,
@ProCount INT,
@ProPrice MONEY AS
INSERT INTO [BioPurchasePro](
[PurchaseID],[ProID],[ProCount],[ProPrice]
)VALUES(
@PurchaseID,@ProID,@ProCount,@ProPrice
)
修改的存储过程：
CREATE PROCEDURE [dbo].[BioPurchasePro_Update]
@PurchaseProID INT,
@PurchaseID INT,
@ProID INT,
@ProCount INT,
@ProPrice MONEY,
@isInvoice BIT,
@isPay BIT,
@InvoicePrice MONEY
AS
UPDATE [BioPurchasePro] SET
[PurchaseID] = @PurchaseID,[ProID] = @ProID,[ProCount] = @ProCount,[ProPrice] = @ProPrice,[isInvoice]
= @isInvoice,[isPay] = @isPay,[InvoicePrice] = @InvoicePrice
WHERE PurchaseProID=@PurchaseProID
产品批号的存储过程：
CREATE PROCEDURE [dbo].[BioPuchaseProBatch_ADD]
@ProBatchID INT OUTPUT,
@purchaseProID INT,
@batchNum NVARCHAR(50),
@boxNum NVARCHAR(20),
@proCount INT,
@realityProCount INT,
@expirationDate DATETIME,
@makeDate DATETIME
AS
INSERT INTO [BioPuchaseProBatch](
[purchaseProID],[batchNum],[boxNum],[proCount],[realityProCount],[expirationDate],[makeDate]
)VALUES(
@purchaseProID,@batchNum,@boxNum,@proCount,@realityProCount,@expirationDate,@makeDate
)
SET @ProBatchID = @@IDENTITY
转换时间的函数：
SELECT CONVERT(NVARCHAR(20),GETDATE(),120)
创建视图：
CREATE VIEW [dbo].[View_BioPurchaseAppInfo]
AS
SELECT
PurchaseID,
Subject,
DepartMentID,
DepartMent=dbo.FN_GetDepartMentByID(DepartMentID),
AppUserId,
AppUserName=dbo.getUserNameByUserID(AppUserId),
RealUserID,
RealUserName=dbo.getUserNameByUserID(RealUserID),
OrderCom,
sendComID,
CopanyName=dbo.getCustomerByID(SendComID),
CONVERT(NVARCHAR(10),ArrivedTime,120) AS ArrivedTime,
CONVERT(NVARCHAR(10),OrderTime,120) AS OrderTime,
sendType,
WholeType,
AcceptUserid,
BillUserID,
BillUserName=dbo.getUserNameByUserID(BillUserID),
BillNo,
DeleteSate
FROM
BioPurchaseAppInfo
根据产品的编号获取产品的名称：
-- Description: 根据产品的编号获取产品的名称
-- =============================================
CREATE FUNCTION [dbo].[FN_getProNameByProID]
(
@ProID INT
)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @ProName NVARCHAR(50)
SELECT @ProName= ProName FROM BiotbProduct WHERE ProID=@ProID
RETURN @ProName
END
创建视图：
CREATE VIEW [dbo].[View_PurchaseProInfo]
AS
SELECT a.*,b.ProBatchID,b.batchNum,b.boxNum,b.proCount AS BatchProCount,b.realityProCount,b.stockDate,
b.expirationDate,b.makeDate,b.isDeleteSate,b.isAuditing,b.isprinted, ProName=dbo.FN_getProNameByProID(a.ProID)
FROM BioPurchasePro AS a INNER JOIN BioPuchaseProBatch AS b
ON b.purchaseProID = a.PurchaseProID