Volian/SourceCode
8
0
Fork 0
Code Issues Pull Requests 1 Projects Releases Wiki Activity
SourceCode/PROMS/SQL/2_Approval_CSLAProcs.sql

1274 lines
33 KiB
Transact-SQL
Raw Permalink Blame History

/****** Object: StoredProcedure [purgeData] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[purgeData]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [purgeData];
GO
CREATE PROCEDURE [dbo].[purgeData]
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
delete from [Checks]
dbcc checkident([Checks],reseed,0)
delete from [Versions]
dbcc checkident([Versions],reseed,0)
delete from [Revisions]
dbcc checkident([Revisions],reseed,0)
delete from [Stages]
dbcc checkident([Stages],reseed,0)
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: purgeData Succeeded'
ELSE PRINT 'Procedure Creation: purgeData Error on Creation'
GO
/****** Object: StoredProcedure [getChecks] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getChecks]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getChecks];
GO
CREATE PROCEDURE [dbo].[getChecks]
WITH EXECUTE AS OWNER
AS
SELECT
[CheckID],
[RevisionID],
[StageID],
[ConsistencyChecks],
[DTS],
[UserID],
[LastChanged]
FROM [Checks]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChecks Succeeded'
ELSE PRINT 'Procedure Creation: getChecks Error on Creation'
GO
/****** Object: StoredProcedure [updateCheck] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateCheck]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateCheck];
GO
CREATE PROCEDURE [dbo].[updateCheck]
(
@CheckID int,
@RevisionID int,
@StageID int,
@ConsistencyChecks xml=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
UPDATE [Checks]
SET
[RevisionID]=@RevisionID,
[StageID]=@StageID,
[ConsistencyChecks]=@ConsistencyChecks,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [CheckID]=@CheckID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Checks] WHERE [CheckID]=@CheckID)
RAISERROR('Check record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Check has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Checks] WHERE [CheckID]=@CheckID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateCheck Succeeded'
ELSE PRINT 'Procedure Creation: updateCheck Error on Creation'
GO
/****** Object: StoredProcedure [addCheck] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addCheck]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addCheck];
GO
CREATE PROCEDURE [dbo].[addCheck]
(
@RevisionID int,
@StageID int,
@ConsistencyChecks xml=null,
@DTS datetime,
@UserID nvarchar(100),
@newCheckID int output,
@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
INSERT INTO [Checks]
(
[RevisionID],
[StageID],
[ConsistencyChecks],
[DTS],
[UserID]
)
VALUES
(
@RevisionID,
@StageID,
@ConsistencyChecks,
@DTS,
@UserID
)
SELECT @newCheckID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Checks] WHERE [CheckID]=@newCheckID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addCheck Succeeded'
ELSE PRINT 'Procedure Creation: addCheck Error on Creation'
GO
/****** Object: StoredProcedure [deleteCheck] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteCheck]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteCheck];
GO
CREATE PROCEDURE [dbo].[deleteCheck]
(
@CheckID int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
DELETE [Checks]
WHERE [CheckID] = @CheckID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteCheck Succeeded'
ELSE PRINT 'Procedure Creation: deleteCheck Error on Creation'
GO
/****** Object: StoredProcedure [getCheck] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCheck]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getCheck];
GO
CREATE PROCEDURE [dbo].[getCheck]
(
@CheckID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[CheckID],
[RevisionID],
[StageID],
[ConsistencyChecks],
[DTS],
[UserID],
[LastChanged]
FROM [Checks]
WHERE [CheckID]=@CheckID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getCheck Succeeded'
ELSE PRINT 'Procedure Creation: getCheck Error on Creation'
GO
/****** Object: StoredProcedure [existsCheck] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsCheck]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsCheck];
GO
CREATE PROCEDURE [dbo].[existsCheck]
(
@CheckID int
)
WITH EXECUTE AS OWNER
AS
SELECT COUNT(*)
FROM [Checks] WHERE [CheckID]=@CheckID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsCheck Succeeded'
ELSE PRINT 'Procedure Creation: existsCheck Error on Creation'
GO
/****** Object: StoredProcedure [getChecksByRevisionID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getChecksByRevisionID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getChecksByRevisionID];
GO
CREATE PROCEDURE [dbo].[getChecksByRevisionID]
(
@RevisionID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[Checks].[CheckID],
[Checks].[RevisionID],
[Checks].[StageID],
[Checks].[ConsistencyChecks],
[Checks].[DTS],
[Checks].[UserID],
[Checks].[LastChanged],
[Stages].[Name] [Stage_Name],
[Stages].[Description] [Stage_Description],
[Stages].[IsApproved] [Stage_IsApproved],
[Stages].[DTS] [Stage_DTS],
[Stages].[UserID] [Stage_UserID]
FROM [Checks]
JOIN [Stages] ON
[Stages].[StageID]=[Checks].[StageID]
WHERE
[Checks].[RevisionID]=@RevisionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChecksByRevisionID Succeeded'
ELSE PRINT 'Procedure Creation: getChecksByRevisionID Error on Creation'
GO
/****** Object: StoredProcedure [getChecksByStageID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getChecksByStageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getChecksByStageID];
GO
CREATE PROCEDURE [dbo].[getChecksByStageID]
(
@StageID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[Checks].[CheckID],
[Checks].[RevisionID],
[Checks].[StageID],
[Checks].[ConsistencyChecks],
[Checks].[DTS],
[Checks].[UserID],
[Checks].[LastChanged],
[Revisions].[ItemID] [Revision_ItemID],
[Revisions].[TypeID] [Revision_TypeID],
[Revisions].[RevisionNumber] [Revision_RevisionNumber],
[Revisions].[RevisionDate] [Revision_RevisionDate],
[Revisions].[Notes] [Revision_Notes],
[Revisions].[Config] [Revision_Config],
[Revisions].[DTS] [Revision_DTS],
[Revisions].[UserID] [Revision_UserID]
FROM [Checks]
JOIN [Revisions] ON
[Revisions].[RevisionID]=[Checks].[RevisionID]
WHERE
[Checks].[StageID]=@StageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChecksByStageID Succeeded'
ELSE PRINT 'Procedure Creation: getChecksByStageID Error on Creation'
GO
/****** Object: StoredProcedure [getRevisions] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRevisions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRevisions];
GO
CREATE PROCEDURE [dbo].[getRevisions]
WITH EXECUTE AS OWNER
AS
SELECT
[RevisionID],
[ItemID],
[TypeID],
[RevisionNumber],
[RevisionDate],
[Notes],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
FROM [Revisions]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRevisions Succeeded'
ELSE PRINT 'Procedure Creation: getRevisions Error on Creation'
GO
/****** Object: StoredProcedure [updateRevision] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateRevision]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateRevision];
GO
CREATE PROCEDURE [dbo].[updateRevision]
(
@RevisionID int,
@ItemID int,
@TypeID int,
@RevisionNumber nvarchar(50)=null,
@RevisionDate datetime=null,
@Notes nvarchar(MAX)=null,
@Config xml=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
UPDATE [Revisions]
SET
[ItemID]=@ItemID,
[TypeID]=@TypeID,
[RevisionNumber]=@RevisionNumber,
[RevisionDate]=@RevisionDate,
[Notes]=@Notes,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [RevisionID]=@RevisionID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Revisions] WHERE [RevisionID]=@RevisionID)
RAISERROR('Revision record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Revision has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Revisions] WHERE [RevisionID]=@RevisionID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateRevision Succeeded'
ELSE PRINT 'Procedure Creation: updateRevision Error on Creation'
GO
/****** Object: StoredProcedure [addRevision] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addRevision]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addRevision];
GO
CREATE PROCEDURE [dbo].[addRevision]
(
@ItemID int,
@TypeID int,
@RevisionNumber nvarchar(50)=null,
@RevisionDate datetime=null,
@Notes nvarchar(MAX)=null,
@Config xml=null,
@DTS datetime,
@UserID nvarchar(100),
@newRevisionID int output,
@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
INSERT INTO [Revisions]
(
[ItemID],
[TypeID],
[RevisionNumber],
[RevisionDate],
[Notes],
[Config],
[DTS],
[UserID]
)
VALUES
(
@ItemID,
@TypeID,
@RevisionNumber,
@RevisionDate,
@Notes,
@Config,
@DTS,
@UserID
)
SELECT @newRevisionID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Revisions] WHERE [RevisionID]=@newRevisionID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addRevision Succeeded'
ELSE PRINT 'Procedure Creation: addRevision Error on Creation'
GO
/****** Object: StoredProcedure [deleteRevision] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteRevision]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteRevision];
GO
CREATE PROCEDURE [dbo].[deleteRevision]
(
@RevisionID int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
DELETE [Checks]
WHERE [RevisionID]=@RevisionID
DELETE [Versions]
WHERE [RevisionID]=@RevisionID
DELETE [Revisions]
WHERE [RevisionID] = @RevisionID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteRevision Succeeded'
ELSE PRINT 'Procedure Creation: deleteRevision Error on Creation'
GO
/****** Object: StoredProcedure [getRevision] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRevision]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRevision];
GO
CREATE PROCEDURE [dbo].[getRevision]
(
@RevisionID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[RevisionID],
[ItemID],
[TypeID],
[RevisionNumber],
[RevisionDate],
[Notes],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
FROM [Revisions]
WHERE [RevisionID]=@RevisionID
SELECT
[Checks].[CheckID],
[Checks].[RevisionID],
[Checks].[StageID],
[Checks].[ConsistencyChecks],
[Checks].[DTS],
[Checks].[UserID],
[Checks].[LastChanged],
[Stages].[Name] [Stage_Name],
[Stages].[Description] [Stage_Description],
[Stages].[IsApproved] [Stage_IsApproved],
[Stages].[DTS] [Stage_DTS],
[Stages].[UserID] [Stage_UserID]
FROM [Checks]
JOIN [Stages] ON
[Stages].[StageID]=[Checks].[StageID]
WHERE
[Checks].[RevisionID]=@RevisionID
SELECT
[Versions].[VersionID],
[Versions].[RevisionID],
[Versions].[StageID],
[Versions].[PDF],
[Versions].[SummaryPDF],
[Versions].[DTS],
[Versions].[UserID],
[Versions].[LastChanged],
[Stages].[Name] [Stage_Name],
[Stages].[Description] [Stage_Description],
[Stages].[IsApproved] [Stage_IsApproved],
[Stages].[DTS] [Stage_DTS],
[Stages].[UserID] [Stage_UserID]
FROM [Versions]
JOIN [Stages] ON
[Stages].[StageID]=[Versions].[StageID]
WHERE
[Versions].[RevisionID]=@RevisionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRevision Succeeded'
ELSE PRINT 'Procedure Creation: getRevision Error on Creation'
GO
/****** Object: StoredProcedure [existsRevision] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsRevision]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsRevision];
GO
CREATE PROCEDURE [dbo].[existsRevision]
(
@RevisionID int
)
WITH EXECUTE AS OWNER
AS
SELECT COUNT(*)
FROM [Revisions] WHERE [RevisionID]=@RevisionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsRevision Succeeded'
ELSE PRINT 'Procedure Creation: existsRevision Error on Creation'
GO
/****** Object: StoredProcedure [getStages] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getStages]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getStages];
GO
CREATE PROCEDURE [dbo].[getStages]
WITH EXECUTE AS OWNER
AS
SELECT
[StageID],
[Name],
[Description],
[IsApproved],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[StageID]=[Stages].[StageID]) [CheckCount],
(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[StageID]=[Stages].[StageID]) [VersionCount]
FROM [Stages]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getStages Succeeded'
ELSE PRINT 'Procedure Creation: getStages Error on Creation'
GO
/****** Object: StoredProcedure [updateStage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateStage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateStage];
GO
CREATE PROCEDURE [dbo].[updateStage]
(
@StageID int,
@Name nvarchar(50),
@Description nvarchar(200)=null,
@IsApproved int,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
UPDATE [Stages]
SET
[Name]=@Name,
[Description]=@Description,
[IsApproved]=@IsApproved,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [StageID]=@StageID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Stages] WHERE [StageID]=@StageID)
RAISERROR('Stage record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Stage has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Stages] WHERE [StageID]=@StageID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateStage Succeeded'
ELSE PRINT 'Procedure Creation: updateStage Error on Creation'
GO
/****** Object: StoredProcedure [addStage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addStage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addStage];
GO
CREATE PROCEDURE [dbo].[addStage]
(
@Name nvarchar(50),
@Description nvarchar(200)=null,
@IsApproved int,
@DTS datetime,
@UserID nvarchar(100),
@newStageID int output,
@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
INSERT INTO [Stages]
(
[Name],
[Description],
[IsApproved],
[DTS],
[UserID]
)
VALUES
(
@Name,
@Description,
@IsApproved,
@DTS,
@UserID
)
SELECT @newStageID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Stages] WHERE [StageID]=@newStageID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addStage Succeeded'
ELSE PRINT 'Procedure Creation: addStage Error on Creation'
GO
/****** Object: StoredProcedure [deleteStage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteStage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteStage];
GO
CREATE PROCEDURE [dbo].[deleteStage]
(
@StageID int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
DELETE [Checks]
WHERE [StageID]=@StageID
DELETE [Versions]
WHERE [StageID]=@StageID
DELETE [Stages]
WHERE [StageID] = @StageID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteStage Succeeded'
ELSE PRINT 'Procedure Creation: deleteStage Error on Creation'
GO
/****** Object: StoredProcedure [getStage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getStage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getStage];
GO
CREATE PROCEDURE [dbo].[getStage]
(
@StageID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[StageID],
[Name],
[Description],
[IsApproved],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[StageID]=[Stages].[StageID]) [CheckCount],
(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[StageID]=[Stages].[StageID]) [VersionCount]
FROM [Stages]
WHERE [StageID]=@StageID
SELECT
[Checks].[CheckID],
[Checks].[RevisionID],
[Checks].[StageID],
[Checks].[ConsistencyChecks],
[Checks].[DTS],
[Checks].[UserID],
[Checks].[LastChanged],
[Revisions].[ItemID] [Revision_ItemID],
[Revisions].[TypeID] [Revision_TypeID],
[Revisions].[RevisionNumber] [Revision_RevisionNumber],
[Revisions].[RevisionDate] [Revision_RevisionDate],
[Revisions].[Notes] [Revision_Notes],
[Revisions].[Config] [Revision_Config],
[Revisions].[DTS] [Revision_DTS],
[Revisions].[UserID] [Revision_UserID]
FROM [Checks]
JOIN [Revisions] ON
[Revisions].[RevisionID]=[Checks].[RevisionID]
WHERE
[Checks].[StageID]=@StageID
SELECT
[Versions].[VersionID],
[Versions].[RevisionID],
[Versions].[StageID],
[Versions].[PDF],
[Versions].[SummaryPDF],
[Versions].[DTS],
[Versions].[UserID],
[Versions].[LastChanged],
[Revisions].[ItemID] [Revision_ItemID],
[Revisions].[TypeID] [Revision_TypeID],
[Revisions].[RevisionNumber] [Revision_RevisionNumber],
[Revisions].[RevisionDate] [Revision_RevisionDate],
[Revisions].[Notes] [Revision_Notes],
[Revisions].[Config] [Revision_Config],
[Revisions].[DTS] [Revision_DTS],
[Revisions].[UserID] [Revision_UserID]
FROM [Versions]
JOIN [Revisions] ON
[Revisions].[RevisionID]=[Versions].[RevisionID]
WHERE
[Versions].[StageID]=@StageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getStage Succeeded'
ELSE PRINT 'Procedure Creation: getStage Error on Creation'
GO
/****** Object: StoredProcedure [existsStage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsStage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsStage];
GO
CREATE PROCEDURE [dbo].[existsStage]
(
@StageID int
)
WITH EXECUTE AS OWNER
AS
SELECT COUNT(*)
FROM [Stages] WHERE [StageID]=@StageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsStage Succeeded'
ELSE PRINT 'Procedure Creation: existsStage Error on Creation'
GO
/****** Object: StoredProcedure [getVersions] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getVersions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getVersions];
GO
CREATE PROCEDURE [dbo].[getVersions]
WITH EXECUTE AS OWNER
AS
SELECT
[VersionID],
[RevisionID],
[StageID],
[PDF],
[SummaryPDF],
[DTS],
[UserID],
[LastChanged]
FROM [Versions]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getVersions Succeeded'
ELSE PRINT 'Procedure Creation: getVersions Error on Creation'
GO
/****** Object: StoredProcedure [updateVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateVersion];
GO
CREATE PROCEDURE [dbo].[updateVersion]
(
@VersionID int,
@RevisionID int,
@StageID int,
@PDF varbinary(MAX)=null,
@SummaryPDF varbinary(MAX)=null,
@DTS datetime,
@UserID nvarchar(200),
@LastChanged timestamp,
@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
UPDATE [Versions]
SET
[RevisionID]=@RevisionID,
[StageID]=@StageID,
[PDF]=@PDF,
[SummaryPDF]=@SummaryPDF,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [VersionID]=@VersionID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Versions] WHERE [VersionID]=@VersionID)
RAISERROR('Version record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Version has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Versions] WHERE [VersionID]=@VersionID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateVersion Succeeded'
ELSE PRINT 'Procedure Creation: updateVersion Error on Creation'
GO
/****** Object: StoredProcedure [addVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addVersion];
GO
CREATE PROCEDURE [dbo].[addVersion]
(
@RevisionID int,
@StageID int,
@PDF varbinary(MAX)=null,
@SummaryPDF varbinary(MAX)=null,
@DTS datetime,
@UserID nvarchar(200),
@newVersionID int output,
@newLastChanged timestamp output
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
INSERT INTO [Versions]
(
[RevisionID],
[StageID],
[PDF],
[SummaryPDF],
[DTS],
[UserID]
)
VALUES
(
@RevisionID,
@StageID,
@PDF,
@SummaryPDF,
@DTS,
@UserID
)
SELECT @newVersionID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Versions] WHERE [VersionID]=@newVersionID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addVersion Succeeded'
ELSE PRINT 'Procedure Creation: addVersion Error on Creation'
GO
/****** Object: StoredProcedure [deleteVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteVersion];
GO
CREATE PROCEDURE [dbo].[deleteVersion]
(
@VersionID int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
BEGIN TRANSACTION
DELETE [Versions]
WHERE [VersionID] = @VersionID
IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit. Top level will rollback
EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteVersion Succeeded'
ELSE PRINT 'Procedure Creation: deleteVersion Error on Creation'
GO
/****** Object: StoredProcedure [getVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getVersion];
GO
CREATE PROCEDURE [dbo].[getVersion]
(
@VersionID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[VersionID],
[RevisionID],
[StageID],
[PDF],
[SummaryPDF],
[DTS],
[UserID],
[LastChanged]
FROM [Versions]
WHERE [VersionID]=@VersionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getVersion Succeeded'
ELSE PRINT 'Procedure Creation: getVersion Error on Creation'
GO
/****** Object: StoredProcedure [existsVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsVersion];
GO
CREATE PROCEDURE [dbo].[existsVersion]
(
@VersionID int
)
WITH EXECUTE AS OWNER
AS
SELECT COUNT(*)
FROM [Versions] WHERE [VersionID]=@VersionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsVersion Succeeded'
ELSE PRINT 'Procedure Creation: existsVersion Error on Creation'
GO
/****** Object: StoredProcedure [getVersionsByRevisionID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getVersionsByRevisionID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getVersionsByRevisionID];
GO
CREATE PROCEDURE [dbo].[getVersionsByRevisionID]
(
@RevisionID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[Versions].[VersionID],
[Versions].[RevisionID],
[Versions].[StageID],
[Versions].[PDF],
[Versions].[SummaryPDF],
[Versions].[DTS],
[Versions].[UserID],
[Versions].[LastChanged],
[Stages].[Name] [Stage_Name],
[Stages].[Description] [Stage_Description],
[Stages].[IsApproved] [Stage_IsApproved],
[Stages].[DTS] [Stage_DTS],
[Stages].[UserID] [Stage_UserID]
FROM [Versions]
JOIN [Stages] ON
[Stages].[StageID]=[Versions].[StageID]
WHERE
[Versions].[RevisionID]=@RevisionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getVersionsByRevisionID Succeeded'
ELSE PRINT 'Procedure Creation: getVersionsByRevisionID Error on Creation'
GO
/****** Object: StoredProcedure [getVersionsByStageID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getVersionsByStageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getVersionsByStageID];
GO
CREATE PROCEDURE [dbo].[getVersionsByStageID]
(
@StageID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[Versions].[VersionID],
[Versions].[RevisionID],
[Versions].[StageID],
[Versions].[PDF],
[Versions].[SummaryPDF],
[Versions].[DTS],
[Versions].[UserID],
[Versions].[LastChanged],
[Revisions].[ItemID] [Revision_ItemID],
[Revisions].[TypeID] [Revision_TypeID],
[Revisions].[RevisionNumber] [Revision_RevisionNumber],
[Revisions].[RevisionDate] [Revision_RevisionDate],
[Revisions].[Notes] [Revision_Notes],
[Revisions].[Config] [Revision_Config],
[Revisions].[DTS] [Revision_DTS],
[Revisions].[UserID] [Revision_UserID]
FROM [Versions]
JOIN [Revisions] ON
[Revisions].[RevisionID]=[Versions].[RevisionID]
WHERE
[Versions].[StageID]=@StageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getVersionsByStageID Succeeded'
ELSE PRINT 'Procedure Creation: getVersionsByStageID Error on Creation'
GO
/****** Object: StoredProcedure [getRevisionsByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRevisionsByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRevisionsByItemID];
GO
CREATE PROCEDURE [dbo].[getRevisionsByItemID]
(
@ItemID int
)
WITH EXECUTE AS OWNER
AS
SELECT
[RevisionID],
[ItemID],
[TypeID],
[RevisionNumber],
[RevisionDate],
[Notes],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
FROM [Revisions]
WHERE [ItemID] = @ItemID
ORDER BY [RevisionID] DESC
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRevisionsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getRevisionsByItemID Error on Creation'
GO
/****** Object: StoredProcedure [getRevisionByItemIDandRevisionNumber] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRevisionByItemIDandRevisionNumber]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRevisionByItemIDandRevisionNumber];
GO
CREATE PROCEDURE [dbo].[getRevisionByItemIDandRevisionNumber]
(
@ItemID int,
@RevisionNumber nvarchar(50)
)
WITH EXECUTE AS OWNER
AS
declare @RevisionID int
set @RevisionID = (select revisionid from revisions where itemid = @itemid and revisionnumber = @RevisionNumber)
SELECT
[RevisionID],
[ItemID],
[TypeID],
[RevisionNumber],
[RevisionDate],
[Notes],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Checks] WHERE [Checks].[RevisionID]=[Revisions].[RevisionID]) [CheckCount],
(SELECT COUNT(*) FROM [Versions] WHERE [Versions].[RevisionID]=[Revisions].[RevisionID]) [VersionCount]
FROM [Revisions]
WHERE [RevisionID]=@RevisionID
SELECT
[Checks].[CheckID],
[Checks].[RevisionID],
[Checks].[StageID],
[Checks].[ConsistencyChecks],
[Stages].[Name] [Stage_Name],
[Stages].[Description] [Stage_Description],
[Stages].[IsApproved] [Stage_IsApproved],
[Stages].[DTS] [Stage_DTS],
[Stages].[UserID] [Stage_UserID]
FROM [Checks]
JOIN [Stages] ON
[Stages].[StageID]=[Checks].[StageID]
WHERE
[Checks].[RevisionID]=@RevisionID
SELECT
[Versions].[VersionID],
[Versions].[RevisionID],
[Versions].[StageID],
[Versions].[DTS],
[Versions].[UserID],
[Versions].[PDF],
[Versions].[SummaryPDF],
[Stages].[Name] [Stage_Name],
[Stages].[Description] [Stage_Description],
[Stages].[IsApproved] [Stage_IsApproved],
[Stages].[DTS] [Stage_DTS],
[Stages].[UserID] [Stage_UserID]
FROM [Versions]
JOIN [Stages] ON
[Stages].[StageID]=[Versions].[StageID]
WHERE
[Versions].[RevisionID]=@RevisionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRevisionByItemIDandRevisionNumber Succeeded'
ELSE PRINT 'Procedure Creation: getRevisionByItemIDandRevisionNumber Error on Creation'
GO
Reference in New Issue View Git Blame Copy Permalink