sansan
凉凉的阿玛尼呀还要擦香香.......

-----新增一条记录,返回新增记录的ID(OutletID 主键,自增)

     (1).创建存储过程

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_VehicleOutlet_ADD]') and              OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[USP_VehicleOutlet_ADD]
  GO
  CREATE PROCEDURE USP_VehicleOutlet_ADD
  @OutletName nvarchar(50),
  @OutletAddress nvarchar(50),
  @OutletPhone nvarchar(50)

   AS 
   INSERT INTO [Traffic_Outlet](
   [OutletName],[OutletAddress],[OutletPhone]
   )VALUES(
   @OutletName,@OutletAddress,@OutletPhone
   )
   RETURN @@IDENTITY
  GO

  (2).SQLServer执行:

  DECLARE @return_value INT
  EXEC @return_value = [dbo].[USP_VehicleOutlet_ADD]
      @OutletName = N'天安门店',
      @OutletAddress = N'天安门',
      @OutletPhone = N'888888'
  SELECT 'Return Value' = @return_value
  GO

-----根据条件删除数据

  (1).创建存储过程

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

    CREATE PROCEDURE USP_VehicleOutlet_Del
    @OutletID int
      AS
    DELETE [Traffic_Outlet]
      WHERE OutletID = @OutletID
    GO

  (2).SQLServer执行:

    DECLARE @return_value int
    EXEC @return_value = [dbo].[USP_VehicleOutlet_Del]
     @OutletID = 9
     SELECT 'Return Value' = @return_value
     GO

-----修改数据

  (1).创建存储过程  

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

    CREATE PROCEDURE USP_VehicleOutlet_Update
    @OutletID int,
    @OutletName nvarchar(50),
    @OutletAddress nvarchar(50),
    @OutletPhone nvarchar(50)
     AS 
     UPDATE [Traffic_Outlet] SET 
     [OutletName] = @OutletName,[OutletAddress] = @OutletAddress,[OutletPhone] = @OutletPhone
     WHERE OutletID=@OutletID
    GO

  (2).SQLServer执行:

    DECLARE @return_value int
    EXEC @return_value = [dbo].[USP_VehicleOutlet_Update]
     @OutletID = 5,
     @OutletName = N'测试修改',
     @OutletAddress = N'东单',
     @OutletPhone = N'888888'
     SELECT 'Return Value' = @return_value
    GO

 -----分页查询

  (1).创建存储过程

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_GeneralInfo_GetPageList]')              and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[USP_GeneralInfo_GetPageList]
    GO
    CREATE PROCEDURE USP_GeneralInfo_GetPageList
    @PageSize INT,
    @CurrPage INT,
    @WhereClause NVARCHAR(1500)
    AS
    DECLARE @StrSQL NVARCHAR(2000)
    IF @WhereClause <> ''
       BEGIN
           SET @StrSQL='SELECT TOP ' + str(@PageSize)+'* FROM BIZ_GeneralInfo WHERE 
                 (InfoId NOT IN (SELECT TOP '+str((@CurrPage-1)*@PageSize)+ ' InfoId FROM                            BIZ_GeneralInfo WHERE '+@WhereClause+' ORDER BY InfoSendDate DESC,InfoId DESC)) 
       AND '+@WhereClause+' ORDER BY InfoSendDate DESC,InfoId DESC'
     END
  ELSE
     BEGIN
        SET @StrSQL='SELECT TOP ' + str(@PageSize)+'* FROM BIZ_GeneralInfo WHERE 
         (InfoId NOT IN (SELECT TOP '+str((@CurrPage-1)*@PageSize)+ ' InfoId FROM BIZ_GeneralInfo
      ORDER BY InfoSendDate DESC,InfoId DESC))
   ORDER BY InfoSendDate DESC,InfoId DESC'
     END
    EXEC (@StrSQL)
  GO

posted on 2010-10-22 14:41  sansan-  阅读(496)  评论(2编辑  收藏  举报