一. 添加记录
需要事务支持,如果上下文环境中不存在事务,新启动一个事务,否则利用已存在的事务.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE
PROCEDURE [dbo].[AddJob]
@jobName nvarchar(50),
@added bit output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @errorCode int
SET @errorCode = 0
--Transaction identity. 1:Created a new transaction. 0:Use old transaction.
DECLARE @tranStarted bit
SET @tranStarted = 0
--Create a new transaction if there is not a transaction.
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @tranStarted = 1
END
ELSE
SET @tranStarted = 0
--Adding operation.
INSERT INTO dbo.Job(JobName)
VALUES (@jobName)
IF ( @@ERROR <> 0 )
BEGIN
SET @errorCode = -1
GOTO Cleanup
END
IF ( @tranStarted = 1 )
BEGIN
SET @tranStarted = 0
COMMIT TRANSACTION
END
SET @added = 1
RETURN(0)
--Error handler.
Cleanup:
IF( @tranStarted = 1 )
BEGIN
SET @tranStarted = 0
ROLLBACK TRANSACTION
END
SET @added = 0
RETURN @errorCode
END
二
.
删除记录
(
同上
)
需要事务支持
,
如果上下文环境中不存在事务
,
新启动一个事务
,
否则利用已存在的事务
.
三
.
修改记录
(
同上
)
需要事务支持
,
如果上下文环境中不存在事务
,
新启动一个事务
,
否则利用已存在的事务
.
四. 查询记录
不需要事务支持.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo]
.
[GetAllJob]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
;
SELECT JobId
,
JobName
FROM Job
END