831 lines
31 KiB
Transact-SQL
831 lines
31 KiB
Transact-SQL
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
|
|
--/**** DBA FUNCTION ****/
|
|
--/* The following can be used as a basis for creating the sql RO database for storing of referenced object for PROMS */
|
|
--/* Note that the default sql database name will be generated in during conversion as the current PROMS database name suffixed with '_RO'. */
|
|
--/* In the script below, the [VE_PROMS_DB_RO] should be replaced with the name that the RO database should be given */
|
|
--/* In the script below, the pathname/filename of the physical database should be modified also. */
|
|
--USE [master]
|
|
--GO
|
|
--/****** Object: Database [VEPROMS_DB_RO] Script Date: 7/23/2019 9:42:54 AM ******/
|
|
--CREATE DATABASE [VEPROMS_DB_RO]
|
|
-- ON PRIMARY
|
|
--( NAME = N'VEPROMS_DB_RO', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS_2012\MSSQL\DATA\VEPROMS_DB_RO.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
|
|
-- LOG ON
|
|
--( NAME = N'VEPROMS_DB_RO_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS_2012\MSSQL\DATA\VEPROMS_DB_RO_log.ldf' , SIZE = 2816KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
|
|
--GO
|
|
--ALTER DATABASE [VEPROMS_DB_RO] SET COMPATIBILITY_LEVEL = 110
|
|
--GO
|
|
--ALTER DATABASE [VEPROMS_DB_RO] set TRUSTWORTHY ON;
|
|
--GO
|
|
--USE VEPROMS_DB_RO
|
|
--GO
|
|
--EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
|
|
--GO
|
|
--sp_configure 'show advanced options', 1;
|
|
--GO
|
|
--RECONFIGURE;
|
|
--GO
|
|
--sp_configure 'clr enabled', 1;
|
|
--GO
|
|
--RECONFIGURE;
|
|
--GO
|
|
--IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
|
|
--begin
|
|
--EXEC [VEPROMS_DB_RO].[dbo].[sp_fulltext_database] @action = 'enable'
|
|
--end
|
|
--GO
|
|
--USE [VEPROMS_DB_RO]
|
|
--GO
|
|
--/**** END OF DBA FUNCTION ****/
|
|
|
|
|
|
-- AFTER THE DATABASE IS CREATED, the following should be executed to create the ROALL table & create the sql stored procedures that */
|
|
-- are used by PROMS, to migrate and access the ro data.
|
|
if db_name() in('master','model','msdn','tempdb')
|
|
begin
|
|
DECLARE @ErrorMsg varchar(255)
|
|
SET @ErrorMsg = 'Don''t add these procedures and functions to ' + db_name()
|
|
PRINT '=========================================================================='
|
|
PRINT ''
|
|
PRINT @ErrorMsg
|
|
PRINT ''
|
|
PRINT 'You probably want to be in the VEPROMS database'
|
|
PRINT ''
|
|
PRINT '=========================================================================='
|
|
RAISERROR (@ErrorMsg, 20, -1) with log
|
|
RETURN
|
|
end
|
|
print 'Adding procedures and functions to ' + db_name()
|
|
GO
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[vlnErrorHandler] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vlnErrorHandler]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [vlnErrorHandler];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[vlnErrorHandler]
|
|
(@ExpectedCount int=-1
|
|
,@MessageFormat nvarchar(512)=N'Expected RowCount (%d) not met (%d)')
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN
|
|
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorProcedure NVARCHAR(126)
|
|
, @ErrorLine INT, @RowCount INT;
|
|
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(),@ErrorNumber = ERROR_NUMBER()
|
|
, @ErrorLine = ERROR_LINE(), @ErrorProcedure = ERROR_PROCEDURE(), @RowCount = @@RowCount;
|
|
IF @ErrorNumber > 0
|
|
BEGIN
|
|
IF @ErrorProcedure = OBJECT_NAME(@@PROCID) -- If the Procedure is the current procedure just pass the error message
|
|
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
|
|
ELSE -- Add in the procedure name and line as well as the error number
|
|
RAISERROR (N'%s[%d] - (%d) %s', @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorNumber, @ErrorMessage)
|
|
END
|
|
ELSE IF @ExpectedCount <> -1 AND @ExpectedCount <> @RowCount
|
|
RAISERROR (@MessageFormat, 16, 1, @ExpectedCount, @RowCount)
|
|
END
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: vlnErrorHandler Succeeded'
|
|
ELSE PRINT 'Procedure Creation: vlnErrorHandler Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[deleteByParid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteByParid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [deleteByParid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[deleteByParid]
|
|
(
|
|
@ROTable char(8),
|
|
@ParentID char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN TRY -- Try Block
|
|
BEGIN TRANSACTION
|
|
DELETE ROALL
|
|
WHERE [ROTable] = @ROTable and [ParentID] = @ParentID
|
|
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: deleteByParid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: deleteByParid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[deleteByRecid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [deleteByRecid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[deleteByRecid]
|
|
(
|
|
@ROTable char(8),
|
|
@RecID char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN TRY -- Try Block
|
|
BEGIN TRANSACTION
|
|
DELETE ROALL
|
|
WHERE [ROTable] = @ROTable and [RecID] = @RecID
|
|
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: deleteByRecid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: deleteByRecid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[deleteByROTable] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteByROTable]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [deleteByROTable];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[deleteByROTable]
|
|
(
|
|
@ROTable char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN TRY -- Try Block
|
|
BEGIN TRANSACTION
|
|
DELETE ROALL
|
|
WHERE [ROTable] = @ROTable
|
|
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: deleteByROTable Succeeded'
|
|
ELSE PRINT 'Procedure Creation: deleteByROTable Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getChildData] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getChildData]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getChildData];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getChildData]
|
|
|
|
(
|
|
@ROTable char(8),
|
|
@ParentID char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT RecID,RecType, isnull(AccPageID, '') AccPageID, Info
|
|
FROM [ROALL]
|
|
where ROTable = @ROTable and (RecType = 3 or RecType = 5) and ParentID=@ParentID ORDER BY RecID ASC
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getChildData Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getChildData Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getCountChildrenOfChildData] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountChildrenOfChildData]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getCountChildrenOfChildData];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getCountChildrenOfChildData]
|
|
(
|
|
@ROTable char(8),
|
|
@ParentID char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT R1.ParentID,COUNT(*) AS CNT
|
|
FROM ROALL R1 INNER JOIN ROALL R2 ON R1.ParentID = R2.RecID
|
|
WHERE (R1.RecType = 3 or R1.RecType = 5) and R1.ROTable = @ROTable and R2.ROTable = @ROTable and R2.ParentID = @ParentID group by R1.ParentID
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getCountChildrenOfChildData Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getCountChildrenOfChildData Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getCountRecidByAccid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountRecidByAccid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getCountRecidByAccid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getCountRecidByAccid]
|
|
(
|
|
@ROTable char(8),
|
|
@AccPageID char(32)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT COUNT (RecID)
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND AccPageID=@AccPageID
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getCountRecidByAccid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getCountRecidByAccid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getCountRecidByAccidNotRecid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountRecidByAccidNotRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getCountRecidByAccidNotRecid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getCountRecidByAccidNotRecid]
|
|
(
|
|
@ROTable char(8),
|
|
@RecID char(8),
|
|
@AccPageID char(32)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT COUNT (RecID)
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND AccPageID=@AccPageID AND (NOT RecID = @RecID)
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getCountRecidByAccidNotRecid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getCountRecidByAccidNotRecid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getCountRecidByParid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountRecidByParid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getCountRecidByParid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getCountRecidByParid]
|
|
|
|
(
|
|
@ROTable char(8),
|
|
@ParentID char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT COUNT (RecID)
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [ParentID]=@ParentID
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getCountRecidByParid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getCountRecidByParid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getCountRectypeByRectype] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountRectypeByRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getCountRectypeByRectype];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getCountRectypeByRectype]
|
|
(
|
|
@ROTable char(8),
|
|
@RecType char(32)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT COUNT (RecType)
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND RecType=@RecType
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getCountRectypeByRectype Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getCountRectypeByRectype Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getGroupAndSubgroups] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGroupAndSubgroups]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getGroupAndSubgroups];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getGroupAndSubgroups]
|
|
|
|
(
|
|
@ROTable char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT RecID, RecType, ParentID, isnull(AccPageID, '') AccPageID, Info
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND (RecType = 3 or RecType = 5) AND ParentID <> '00000002'
|
|
ORDER BY ParentID,RecID ASC
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getGroupAndSubgroups Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getGroupAndSubgroups Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getInfoByParidRectype] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getInfoByParidRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getInfoByParidRectype];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getInfoByParidRectype]
|
|
|
|
(
|
|
@ROTable char(8),
|
|
@ParentID char(8),
|
|
@RecType int
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [Info]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [ParentID]=@ParentID AND [RecType]=@RecType
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getInfoByParidRectype Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getInfoByParidRectype Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getInfoByRecid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getInfoByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getInfoByRecid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getInfoByRecid]
|
|
(
|
|
@ROTable char(8),
|
|
@RecID char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [Info]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [RecID]=@RecID
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getInfoByRecid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getInfoByRecid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getInfoByRecidRectype] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getInfoByRecidRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getInfoByRecidRectype];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getInfoByRecidRectype]
|
|
(
|
|
@ROTable char(8),
|
|
@RecID char(8),
|
|
@RecType int
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [Info]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [RecID]=@RecID AND [RecType]=@RecType
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getInfoByRecidRectype Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getInfoByRecidRectype Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getParidAccidInfoByRecid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParidAccidInfoByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getParidAccidInfoByRecid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getParidAccidInfoByRecid]
|
|
(
|
|
@ROTable char(8),
|
|
@RecId char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [ParentID], [AccPageID], [Info]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [RecID]=@RecId
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getParidAccidInfoByRecid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getParidAccidInfoByRecid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getParidAccidInfoByRectypeParid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParidAccidInfoByRectypeParid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getParidAccidInfoByRectypeParid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getParidAccidInfoByRectypeParid]
|
|
(
|
|
@ROTable char(8),
|
|
@RecType int,
|
|
@ParentID char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [ParentID], [AccPageID], [Info]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [RecType]=@RecType AND [ParentID]= @ParentID
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getParidAccidInfoByRectypeParid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getParidAccidInfoByRectypeParid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getRecidByRectypeInfo] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRecidByRectypeInfo]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getRecidByRectypeInfo];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getRecidByRectypeInfo]
|
|
(
|
|
@ROTable char(8),
|
|
@RecType int,
|
|
@mstrecid nvarchar(max)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [RecID]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [RecType]=@RecType AND Info like @mstrecid
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getRecidByRectypeInfo Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getRecidByRectypeInfo Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getRecidInfoByRectype] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRecidInfoByRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getRecidInfoByRectype];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getRecidInfoByRectype]
|
|
(
|
|
@ROTable char(8),
|
|
@RecType int
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [RecID], [Info]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [RecType]=@RecType
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getRecidInfoByRectype Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getRecidInfoByRectype Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getRecidInfoByRectypeAsc] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRecidInfoByRectypeAsc]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getRecidInfoByRectypeAsc];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getRecidInfoByRectypeAsc]
|
|
(
|
|
@ROTable char(8),
|
|
@RecType int
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [RecID], [Info]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [RecType]=@RecType
|
|
ORDER BY RecID ASC
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getRecidInfoByRectypeAsc Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getRecidInfoByRectypeAsc Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[getRecidInfoByRectypeParidAsc] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRecidInfoByRectypeParidAsc]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [getRecidInfoByRectypeParidAsc];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[getRecidInfoByRectypeParidAsc]
|
|
(
|
|
@ROTable char(8),
|
|
@RecType int,
|
|
@ParentID char(8)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
SELECT [RecID], [Info]
|
|
FROM [ROALL]
|
|
WHERE [ROTable]=@ROTable AND [RecType]=@RecType AND [ParentID]=@ParentID
|
|
ORDER BY RecID ASC
|
|
RETURN
|
|
GO
|
|
-- Display the status of Proc creation
|
|
IF (@@Error = 0) PRINT 'Procedure Creation: getRecidInfoByRectypeParidAsc Succeeded'
|
|
ELSE PRINT 'Procedure Creation: getRecidInfoByRectypeParidAsc Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[insertAllRectypes] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[insertAllRectypes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [insertAllRectypes];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[insertAllRectypes]
|
|
(
|
|
@ROTable char(8),
|
|
@RecID char(8),
|
|
@RecType int,
|
|
@ParentID char(8),
|
|
@AccPageID char(32),
|
|
@Info nvarchar(max),
|
|
@ModDateTime char(14)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN TRY -- Try Block
|
|
BEGIN TRANSACTION
|
|
INSERT INTO [ROALL]
|
|
(
|
|
ROTable,
|
|
RecID,
|
|
RecType,
|
|
ParentID,
|
|
ModDateTime,
|
|
AccPageID,
|
|
Info
|
|
)
|
|
VALUES
|
|
(
|
|
@ROTable,
|
|
@RecID,
|
|
@RecType,
|
|
@ParentID,
|
|
@ModDateTime,
|
|
@AccPageID,
|
|
@Info
|
|
)
|
|
|
|
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: insertAllRectypes Succeeded'
|
|
ELSE PRINT 'Procedure Creation: insertAllRectypes Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[updateInfoAccidByRecid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateInfoAccidByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [updateInfoAccidByRecid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[updateInfoAccidByRecid]
|
|
(
|
|
@ROTable char(8),
|
|
@RecID char(8),
|
|
@AccPageID char(32),
|
|
@Info nvarchar(max),
|
|
@ModDateTime char(14),
|
|
@ParentID varchar(8) = NULL
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN TRY -- Try Block
|
|
BEGIN TRANSACTION
|
|
UPDATE [ROALL]
|
|
SET
|
|
[Info]=@Info,
|
|
[ModDateTime]=@ModDateTime,
|
|
[AccPageID]=@AccPageID,
|
|
[ParentID]=ISNULL(@ParentID, ParentID)
|
|
WHERE [ROTable]=@ROTable AND [RecID]=@RecID
|
|
IF @@ROWCOUNT = 0
|
|
BEGIN
|
|
IF NOT exists(select * from [ROALL] WHERE [ROTable]=@ROTable AND [RecID]=@RecID)
|
|
RAISERROR('ROALL record has been deleted by another user', 16, 1)
|
|
ELSE
|
|
RAISERROR('ROALL has been edited by another user', 16, 1)
|
|
END
|
|
|
|
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: updateInfoAccidByRecid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: updateInfoAccidByRecid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[updateInfoByRecid] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateInfoByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [updateInfoByRecid];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[updateInfoByRecid]
|
|
(
|
|
@ROTable char(8),
|
|
@RecID char(8),
|
|
@Info nvarchar(max),
|
|
@ModDateTime char(14)
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN TRY -- Try Block
|
|
BEGIN TRANSACTION
|
|
UPDATE [ROALL]
|
|
SET
|
|
[Info]=@Info,
|
|
[ModDateTime]=@ModDateTime
|
|
WHERE [ROTable]=@ROTable AND [RecID]=@RecID
|
|
IF @@ROWCOUNT = 0
|
|
BEGIN
|
|
IF NOT exists(select * from [ROALL] WHERE [ROTable]=@ROTable AND [RecID]=@RecID)
|
|
RAISERROR('ROALL record has been deleted by another user', 16, 1)
|
|
ELSE
|
|
RAISERROR('ROALL has been edited by another user', 16, 1)
|
|
END
|
|
|
|
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: updateInfoByRecid Succeeded'
|
|
ELSE PRINT 'Procedure Creation: updateInfoByRecid Error on Creation'
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[updateInfoByRecidRectype] ******/
|
|
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateInfoByRecidRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
|
|
DROP PROCEDURE [updateInfoByRecidRectype];
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE PROCEDURE [dbo].[updateInfoByRecidRectype]
|
|
(
|
|
@ROTable char(8),
|
|
@RecID char(8),
|
|
@Info nvarchar(max),
|
|
@ModDateTime char(14),
|
|
@RecType int
|
|
)
|
|
WITH EXECUTE AS OWNER
|
|
AS
|
|
BEGIN TRY -- Try Block
|
|
BEGIN TRANSACTION
|
|
UPDATE [ROALL]
|
|
SET
|
|
[Info]=@Info,
|
|
[ModDateTime]=@ModDateTime
|
|
WHERE [ROTable]=@ROTable AND [RecID]=@RecID AND [RecType]=@RecType
|
|
IF @@ROWCOUNT = 0
|
|
BEGIN
|
|
IF NOT exists(select * from [ROALL] WHERE [ROTable]=@ROTable AND [RecID]=@RecID AND [RecType]=@RecType)
|
|
RAISERROR('ROALL record has been deleted by another user', 16, 1)
|
|
ELSE
|
|
RAISERROR('ROALL has been edited by another user', 16, 1)
|
|
END
|
|
|
|
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: updateInfoByRecidRectype Succeeded'
|
|
ELSE PRINT 'Procedure Creation: updateInfoByRecidRectype Error on Creation'
|
|
GO
|
|
|
|
/****** Object: Table [dbo].[ROALL] ******/
|
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ROALL]') AND type in (N'U'))
|
|
DROP TABLE [dbo].[ROALL]
|
|
GO
|
|
/*****************************************************************************
|
|
Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
|
|
Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
|
|
*****************************************************************************/
|
|
CREATE TABLE [dbo].[ROALL](
|
|
[ROTable] [char](8) NOT NULL,
|
|
[RecID] [char](8) NOT NULL,
|
|
[RecType] [int] NOT NULL,
|
|
[ParentID] [char](8) NOT NULL,
|
|
[AccPageID] [char](32) NULL,
|
|
[ModDateTime] [char](14) NOT NULL,
|
|
[Info] [nvarchar](max) NULL,
|
|
CONSTRAINT [PK_ROMASTER] PRIMARY KEY CLUSTERED
|
|
(
|
|
[ROTable] ASC,
|
|
[RecID] ASC
|
|
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
|
|
-- Display the status of Table creation
|
|
IF (@@Error = 0) PRINT 'Table Creation: ROALL Succeeded'
|
|
ELSE PRINT 'Table Creation: DeleteLog ROALL on Creation'
|
|
GO
|
|
|
|
|