通用存储过程的编写
对数据库的操作基本上就四种： 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