2.sql

网上购物管理系统做的很一般 见谅

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