?? 2.sql

?? 網上購物管理系統做的很一般見諒
?? SQL
?? 第 1 頁 / 共 4 頁
字號:
12 3 4 下一頁
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuyAddNew]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BuyAddNew]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuyDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BuyDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuyUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BuyUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExistByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ExistByID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExistByName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ExistByName]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExistNameWithoutID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ExistNameWithoutID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GoodsAddNew]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GoodsAddNew]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GoodsDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GoodsDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GoodsTypeAddNew]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GoodsTypeAddNew]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GoodsTypeDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GoodsTypeDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GoodsTypeUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GoodsTypeUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GoodsUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GoodsUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IsValidUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[IsValidUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaleAddNew]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaleAddNew]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaleDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaleDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaleUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaleUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpoilageAddNew]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SpoilageAddNew]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpoilageDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SpoilageDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpoilageUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SpoilageUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SupplierAddNew]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SupplierAddNew]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SupplierDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SupplierDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SupplierUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SupplierUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserAddNew]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserAddNew]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserUpdate]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*添加一條進貨信息，輸出進貨編號和執行結果*/
CREATE PROC dbo.BuyAddNew
(
    @GoodsID      int,              --商品編號
    @Amount       decimal(18, 2),   --進貨數量
    @UnitPrice    smallmoney,       --單價
    @Deliverer    varchar(10),      --送貨員
    @Transactor   varchar(10),      --辦理員
    @RegistrarID  int,              --登記員編號
    @Remark       varchar(1000),    --備注
    @ID           int OUTPUT,       --進貨編號
    @ReturnValue  int OUTPUT        --執行結果（VB組件的自定義枚舉值）
)
AS
    DECLARE @ErrNo int              --保存錯誤號
    BEGIN TRANSACTION               --開始事務

      /*
      手動維護數據完整性的代碼。如果沒有創建外鍵約束，請取消這段代碼的注釋
      驗證是否存在所進商品的編號，若否則不能添加進貨信息
      DECLARE @ExistID bit
      EXEC ExistByID 'Goods', 'GoodsID', @GoodsID, @ExistID OUTPUT
      IF @ExistID = 0               --商品編號不存在
         BEGIN
           ROLLBACK TRANSACTION     --回滾事務
           SELECT @ReturnValue = 2  --返回VB組件的自定義枚舉值
           RETURN                   
         END
      */
 
      /*添加記錄*/
      INSERT INTO Buy Values(@GoodsID, @Amount, @UnitPrice, @Deliverer, @Transactor, @RegistrarID, GetDate(), @Remark)
      
      --記錄當前的錯誤號
      SELECT @ErrNo = @@ERROR

      /*輸出參數*/
      IF @ErrNo = 0                 --沒有發生錯誤
         BEGIN
           SELECT @ID = (SELECT MAX(BuyID) FROM Buy)
           SELECT @ReturnValue = 0
         END
      ELSE IF @ErrNo = 547          --發生外鍵沖突
         BEGIN
           ROLLBACK TRANSACTION     --遇到錯誤則回滾事務         
           SELECT @ReturnValue = 2
           RETURN
         END
      ELSE                          --其他未知錯誤
         BEGIN
           ROLLBACK TRANSACTION     --遇到錯誤則回滾事務 
           SELECT @ReturnValue = 1        
           RETURN
         END

    COMMIT TRANSACTION              --提交事務


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*刪除一條進貨信息，輸出執行結果*/
CREATE PROC dbo.BuyDelete
(
    @BuyID         int,             --進貨編號
    @ReturnValue   int OUTPUT       --執行結果（VB組件的自定義枚舉值）
)
AS
    DECLARE @ErrNo int              --保存錯誤號
    BEGIN TRANSACTION               --開始事務

      /*驗證是否存在所要刪除進貨信息的編號，若否則不能刪除*/
      DECLARE @ExistID bit
      EXEC ExistByID 'Buy', 'BuyID', @BuyID, @ExistID OUTPUT
      IF @ExistID = 0               --進貨編號不存在
         BEGIN
           ROLLBACK TRANSACTION     --回滾事務
           SELECT @ReturnValue = 2  --返回VB組件的自定義枚舉值
           RETURN                   --結束存儲過程
         END
      
      /*
      手動維護數據完整性的代碼。如果沒有創建外鍵約束，請取消這段代碼的注釋
      驗證報損記錄是否引用了該進貨編號，若是則不能刪除
      EXEC ExistByID 'Spoilage', 'BuyID', @BuyID, @ExistID OUTPUT
      IF @ExistID = 1               --引用了進貨編號
         BEGIN
           ROLLBACK TRANSACTION     --回滾事務
           SELECT @ReturnValue = 3  --返回VB組件的自定義枚舉值
           RETURN                   --結束存儲過程
         END
      */
 
      /*刪除記錄*/
      DELETE FROM Buy WHERE BuyID = @BuyID

      --記錄當前的錯誤號
      SELECT @ErrNo = @@ERROR

      /*輸出參數*/
      IF @ErrNo = 0                 --沒有發生錯誤
         SELECT @ReturnValue = 0
      ELSE IF @ErrNo = 547          --發生外鍵沖突
         BEGIN
           ROLLBACK TRANSACTION     --遇到錯誤則回滾事務         
           SELECT @ReturnValue = 3
           RETURN
         END
      ELSE                          --其他未知錯誤
         BEGIN
           ROLLBACK TRANSACTION     --遇到錯誤則回滾事務 
           SELECT @ReturnValue = 1        
           RETURN
         END

    COMMIT TRANSACTION              --提交事務


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*更新一條進貨信息，輸出執行結果*/
CREATE PROC dbo.BuyUpdate
(
    @BuyID        int,            --進貨編號
    @GoodsID      int,            --商品編號
    @Amount       decimal(18, 2), --進貨數量
    @UnitPrice    smallmoney,     --單價
    @Deliverer    varchar(10),    --送貨員
    @Transactor   varchar(10),    --辦理員
    @RegistrarID  int,            --登記員編號
    @Remark       varchar(1000),  --備注
    @ReturnValue  int OUTPUT      --執行結果（VB組件的自定義枚舉值）
)
AS
    DECLARE @ErrNo int              --保存錯誤號
    BEGIN TRANSACTION               --開始事務

      /*驗證是否存在進貨編號，若否則不能更新*/
      DECLARE @ExistID bit
      EXEC ExistByID 'Buy', 'BuyID', @BuyID, @ExistID OUTPUT
      IF @ExistID = 0               --進貨編號不存在
         BEGIN
           ROLLBACK TRANSACTION     --回滾事務
           SELECT @ReturnValue = 2  --返回VB組件的自定義枚舉值
           RETURN                   --結束存儲過程
         END

      /*更新記錄*/
      UPDATE Buy SET GoodsID = @GoodsID, Amount = @Amount, UnitPrice = @UnitPrice, Deliverer = @Deliverer, Transactor = @Transactor, RegistrarID = @RegistrarID, RegDate = GetDate(), Remark = @Remark WHERE BuyID = @BuyID

      --記錄當前的錯誤號
      SELECT @ErrNo = @@ERROR

      /*輸出參數*/
      IF @ErrNo = 0                 --沒有發生錯誤
         SELECT @ReturnValue = 0
      ELSE                          --其他未知錯誤
         BEGIN
           ROLLBACK TRANSACTION     --遇到錯誤則回滾事務 
           SELECT @ReturnValue = 1        
           RETURN
         END

    COMMIT TRANSACTION              --提交事務


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*查看某個數據表中，是否存在某個整型字段等于某個值的記錄*/
CREATE PROC dbo.ExistByID
(  
    @strTableName  varchar(255),            --表名
    @strFieldName  varchar(255),            --整型字段名
    @intFieldValue int,                     --整型字段值
    @bitResult     bit OUTPUT               --輸出值，0為不存在，1為存在
)
AS
    SET NOCOUNT ON
    DECLARE @strSQL varchar(3000)           --主語句
    DECLARE @nCount int			    --返回記錄行數

    /*構建存儲符合條件的記錄數的臨時表*/
    IF OBJECT_ID('dbo.#tmpTable') IS NULL
       CREATE TABLE #tmpTable(tmpField int) --創建臨時表
    ELSE
       TRUNCATE TABLE #tmpTable             --清空臨時表

    /*在數據庫表中檢索符合條件的記錄數并存儲在臨時表中*/
    SELECT @strSQL = 'SELECT COUNT([' + @strFieldName + ']) FROM [' + @strTableName + '] WHERE [' + @strFieldName + '] = ' + CONVERT(varchar(50),@intFieldValue)
    SELECT @strSQl = 'INSERT #tmpTable ' + @strSQL
    EXEC(@strSQL)

    /*在臨時表中返回存儲的記錄數*/
    SELECT TOP 1 @nCount = tmpField FROM #tmpTable ORDER BY tmpField

    /*刪除臨時表*/
    DROP TABLE #tmpTable

    /*輸出參數*/
    IF @nCount > 0
       SELECT @bitResult = 1
    ELSE
       SELECT @bitResult = 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*查看某個數據表中，是否存在某個字符型字段等于某個值的記錄*/
CREATE PROC dbo.ExistByName
(
    @strTableName  varchar(255),            --表名
    @strFieldName  varchar(255),            --字符型字段名
    @strFieldValue varchar(50),             --字符型字段值
    @bitResult     bit OUTPUT               --輸出值，0為不存在，1為存在
)
AS
    SET NOCOUNT ON
    DECLARE @strSQL varchar(3000)           --主語句
    DECLARE @nCount int			    --返回記錄行數

    /*創建存儲符合條件的記錄數的臨時表*/
    IF OBJECT_ID('dbo.#tmpTable') IS NULL
       CREATE TABLE #tmpTable(tmpField int) --創建臨時表
    ELSE
       TRUNCATE TABLE #tmpTable             --清空臨時表

    /*在數據庫表中檢索符合條件的記錄數并存儲在臨時表中*/
    SELECT @strSQL = 'SELECT COUNT([' + @strFieldName + ']) FROM [' + @strTableName + '] WHERE [' + @strFieldName + '] = ''' + @strFieldValue + ''''
    SELECT @strSQl = 'INSERT #tmpTable ' + @strSQL
    EXEC(@strSQL)

    /*在臨時表中返回存儲的記錄數*/
    SELECT TOP 1 @nCount = tmpField FROM #tmpTable ORDER BY tmpField

    /*刪除臨時表*/
    DROP TABLE #tmpTable

    /*輸出參數*/
    IF @nCount > 0
       SELECT @bitResult = 1
    ELSE
       SELECT @bitResult = 0


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*查看某個數據表中，是否存在ID字段不為某個值但某個字符型字段等于某個值的記錄*/
CREATE PROC dbo.ExistNameWithoutID
(
    @strTableName  varchar(255),            --表名
    @strIDName     varchar(255),            --ID字段名
    @intIDValue    int,                     --ID字段值
    @strFieldName  varchar(255),            --字符型字段名
    @strFieldValue varchar(50),             --字符型字段值
    @bitResult     bit OUTPUT               --輸出值，0為不存在，1為存在
)
AS
    SET NOCOUNT ON
    DECLARE @strSQL varchar(3000)           --主語句
    DECLARE @nCount int			    --返回記錄行數

    /*創建存儲符合條件的記錄數的臨時表*/
    IF OBJECT_ID('dbo.#tmpTable') IS NULL
       CREATE TABLE #tmpTable(tmpField int) --創建臨時表
    ELSE
       TRUNCATE TABLE #tmpTable             --清空臨時表

    /*在數據庫表中檢索符合條件的記錄數并存儲在臨時表中*/
    SELECT @strSQL = 'SELECT COUNT([' + @strFieldName + ']) FROM [' + @strTableName + '] WHERE [' + @strFieldName + '] = ''' + @strFieldValue + '''' + ' AND ' + @strIDName + ' <> ' + CONVERT(varchar(50),@intIDValue)
    SELECT @strSQl = 'INSERT #tmpTable ' + @strSQL
    EXEC(@strSQL)

    /*在臨時表中返回存儲的記錄數*/
    SELECT TOP 1 @nCount = tmpField FROM #tmpTable ORDER BY tmpField

    /*刪除臨時表*/
    DROP TABLE #tmpTable
12 3 4 下一頁
?? 文件大小 1102 K
?? 上傳用戶 likefunv
?? 所屬分類百貨/超市行業
??? 相關標簽

#網上購物 #管理系統
?? 快捷鍵說明

復制代碼 Ctrl + C
搜索代碼 Ctrl + F
全屏模式 F11
切換主題 Ctrl + Shift + D
顯示快捷鍵 ?
增大字號 Ctrl + =
減小字號 Ctrl + -
亚洲欧美第一页_禁久久精品乱码_粉嫩av一区二区三区免费野_久草精品视频

?? 2.sql

?? 快捷鍵說明
