|
通用存储过程的编写
对数据库的操作基本上就四种：Insert、Update、Delete和Select，而Update和Insert两种操作又可以作简单的合并，这样下来，基本上一个数据表对应三个存储过程便可以完成绝大多数的数据库操作。存储过程命名规则：Operate_TableName。比如表Order_Info对应三个存储过程：AddEdit_Order_Info、Delete_Order_Info、Search_Order_Info，下面先列出相关代码，然后作总体分析。
一、AddEdit_Order_Info
/*************************************************************
** Name : AddEdit_Order_Info
** Creater : PPCoder Designed By PPCode Studio(PPTech.Net)
** Create Date : 2004-9-6 8:30:17
** Modifer : Rexsp
** Modify Date : 2004-9-6 8:30:17
** Description : AddEdit information for Order_Info
**************************************************************/
ALTER PROCEDURE dbo.AddEdit_Order_Info
(
@OrderStateID Int = -1,
@OrderStateID_Min Int = -1,
@OrderStateID_Max Int = -1,
@OrderUserID Int = -1,
@OrderUserID_Min Int = -1,
@OrderUserID_Max Int = -1,
@OrderID Int = -1,
@OrderID_Min Int = -1,
@OrderID_Max Int = -1,
@ProductID Int = -1,
@ProductID_Min Int = -1,
@ProductID_Max Int = -1,
@CustomizeID Int = -1,
@CustomizeID_Min Int = -1,
@CustomizeID_Max Int = -1,
@OutID INT = 0 OUTPUT
)
AS
IF @OrderID=-1
BEGIN
INSERT INTO [Order_Info] (
[OrderStateID],
[OrderUserID],
[ProductID],
[CustomizeID]
)
VALUES(
@OrderStateID,
@OrderUserID,
@ProductID,
@CustomizeID
)
Set @OutID = @@IDENTITY
END
ELSE
BEGIN
DECLARE @strSQL NVARCHAR(1000)
SET @strSQL = 'UPDATE [Order_Info] SET @tmpOrderID = @tmpOrderID'
IF @OrderStateID <> -1
BEGIN
SET @strSQL = @strSQL + ', [OrderStateID] = @tmpOrderStateID'
END
IF @OrderUserID <> -1
BEGIN
SET @strSQL = @strSQL + ', [OrderUserID] = @tmpOrderUserID'
END
IF @ProductID <> -1
BEGIN
SET @strSQL = @strSQL + ', [ProductID] = @tmpProductID'
END
IF @CustomizeID <> -1
BEGIN
SET @strSQL = @strSQL + ', [CustomizeID] = @tmpCustomizeID'
END
SET @strSQL = @strSQL + ' WHERE [OrderID] = @tmpOrderID'
BEGIN TRAN
EXECUTE sp_executesql @strSQL, N'
@tmpOrderStateID INT,
@tmpOrderUserID INT,
@tmpOrderID INT,
@tmpProductID INT,
@tmpCustomizeID INT',
@tmpOrderStateID=@OrderStateID,
@tmpOrderUserID=@OrderUserID,
@tmpOrderID=@OrderID,
@tmpProductID=@ProductID,
@tmpCustomizeID=@CustomizeID
Set @OutID = @OrderID
IF @@error!=0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
END
RETURN
二、Delete_Order_Info
/*************************************************************
** Name : Delete_Order_Info
** Creater : PPCoder Designed By PPCode Studio(PPTech.Net)
** Create Date : 2004-9-6 8:30:17
** Modifer : Rexsp
** Modify Date : 2004-9-6 8:30:17
** Description : Delete information for Order_Info
**************************************************************/
ALTER PROCEDURE dbo.Delete_Order_Info
(
@OrderStateID Int = -1,
@OrderStateID_Min Int = -1,
@OrderStateID_Max Int = -1,
@OrderUserID Int = -1,
@OrderUserID_Min Int = -1,
@OrderUserID_Max Int = -1,
@OrderID Int = -1,
@OrderID_Min Int = -1,
@OrderID_Max Int = -1,
@ProductID Int = -1,
@ProductID_Min Int = -1,
@ProductID_Max Int = -1,
@CustomizeID Int = -1,
@CustomizeID_Min Int = -1,
@CustomizeID_Max Int = -1,
@OutID INT = 0 OUTPUT
)
AS
DECLARE @strSQL NVARCHAR(1000)
SET @strSQL = 'DELETE FROM [Order_Info] WHERE @tmpOrderID = @tmpOrderID '
IF @OrderStateID<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderStateID = @tmpOrderStateID'
END
IF @OrderStateID_Min<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderStateID_Min = @tmpOrderStateID_Min'
END
IF @OrderStateID_Max<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderStateID_Max = @tmpOrderStateID_Max'
END
IF @OrderUserID<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderUserID = @tmpOrderUserID'
END
IF @OrderUserID_Min<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderUserID_Min = @tmpOrderUserID_Min'
END
IF @OrderUserID_Max<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderUserID_Max = @tmpOrderUserID_Max'
END
IF @OrderID<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderID = @tmpOrderID'
END
IF @OrderID_Min<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderID_Min = @tmpOrderID_Min'
END
IF @OrderID_Max<>-1
BEGIN
SET @strSQL = @strSQL + ' AND OrderID_Max = @tmpOrderID_Max'
END
IF @ProductID<>-1
BEGIN
SET @strSQL = @strSQL + ' AND ProductID = @tmpProductID'
END
IF @ProductID_Min<>-1
BEGIN
SET @strSQL = @strSQL + ' AND ProductID_Min = @tmpProductID_Min'
END
IF @ProductID_Max<>-1
BEGIN
SET @strSQL = @strSQL + ' AND ProductID_Max = @tmpProductID_Max'
END
IF @CustomizeID<>-1
BEGIN
SET @strSQL = @strSQL + ' AND CustomizeID = @tmpCustomizeID'
END
IF @CustomizeID_Min<>-1
BEGIN
SET @strSQL = @strSQL + ' AND CustomizeID_Min = @tmpCustomizeID_Min'
END
IF @CustomizeID_Max<>-1
BEGIN
SET @strSQL = @strSQL + ' AND CustomizeID_Max = @tmpCustomizeID_Max'
END
BEGIN TRAN
EXECUTE sp_executesql @strSQL, N'
@tmpOrderStateID INT,
@tmpOrderUserID INT,
@tmpOrderID INT,
@tmpProductID INT,
@tmpCustomizeID INT',
@tmpOrderStateID=@OrderStateID,
@tmpOrderUserID=@OrderUserID,
@tmpOrderID=@OrderID,
@tmpProductID=@ProductID,
@tmpCustomizeID=@CustomizeID
Set @OutID = @OrderID
IF @@error!=0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN
三、 Search_Order_Info
/*************************************************************
** Name : Search_Order_Info
** Creater : PPCoder Designed By PPCode Studio(PPTech.Net)
** Create Date : 2004-9-6 8:30:17
** Modifer : Rexsp
** Modify Date : 2004-9-6 8:30:17
** Description : Search information for Order_Info
**************************************************************/
ALTER PROCEDURE dbo.Search_Order_Info
(
@OrderStateID Int = -1,
@OrderStateID_Min Int = -1,
@OrderStateID_Max Int = -1,
@OrderUserID Int = -1,
@OrderUserID_Min Int = -1,
@OrderUserID_Max Int = -1,
@OrderID Int = -1,
@OrderID_Min Int = -1,
@OrderID_Max Int = -1,
@ProductID Int = -1,
@ProductID_Min Int = -1,
@ProductID_Max Int = -1,
@CustomizeID Int = -1,
@CustomizeID_Min Int = -1,
@CustomizeID_Max Int = -1,
@ReturnCount INT=-1,
| 
