-----新增一条记录,返回新增记录的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
