Volian/SourceCode
8
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
Files
B2021-092
SourceCode/PROMS/VEPROMS.CSLA.Library/Sql commands/1.CSLA_StoredProcedures_20090325.SQL

7796 lines
198 KiB
Transact-SQL
Raw Permalink Blame History

IF not EXISTS (select * from syscolumns where id = (select object_id('Documents', 'local')) and Name='FileExtension')
Alter Table Documents ADD [FileExtension] [nvarchar](10) NOT NULL CONSTRAINT [DF_Documents_FileExtension] DEFAULT (N'.Doc')
GO
/****** 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]
AS
delete from [Annotations]
dbcc checkident([Annotations],reseed,0)
delete from [AnnotationTypes]
dbcc checkident([AnnotationTypes],reseed,0)
delete from [Assignments]
dbcc checkident([Assignments],reseed,0)
delete from [Associations]
dbcc checkident([Associations],reseed,0)
delete from [Details]
dbcc checkident([Details],reseed,0)
delete from [DocVersions]
dbcc checkident([DocVersions],reseed,0)
delete from [Entries]
delete from [Figures]
dbcc checkident([Figures],reseed,0)
delete from [Folders]
dbcc checkident([Folders],reseed,0)
delete from [Memberships]
dbcc checkident([Memberships],reseed,0)
delete from [Parts]
delete from [Permissions]
dbcc checkident([Permissions],reseed,0)
delete from [ROFsts]
dbcc checkident([ROFsts],reseed,0)
delete from [ROImages]
dbcc checkident([ROImages],reseed,0)
delete from [Roles]
dbcc checkident([Roles],reseed,0)
delete from [RoUsages]
dbcc checkident([RoUsages],reseed,0)
delete from [Users]
dbcc checkident([Users],reseed,0)
delete from [ZContents]
delete from [ZTransitions]
delete from [Connections]
dbcc checkident([Connections],reseed,0)
delete from [Documents]
dbcc checkident([Documents],reseed,0)
delete from [Groups]
dbcc checkident([Groups],reseed,0)
delete from [RODbs]
dbcc checkident([RODbs],reseed,0)
delete from [Transitions]
dbcc checkident([Transitions],reseed,0)
delete from [Items]
dbcc checkident([Items],reseed,0)
delete from [Contents]
dbcc checkident([Contents],reseed,0)
delete from [Formats]
dbcc checkident([Formats],reseed,0)
RETURN
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 [getAnnotations] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotations]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAnnotations];
GO
CREATE PROCEDURE [dbo].[getAnnotations]
AS
SELECT
[AnnotationID],
[ItemID],
[TypeID],
[RtfText],
[SearchText],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Annotations]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotations Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotations Error on Creation'
GO
/****** Object: StoredProcedure [updateAnnotation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateAnnotation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateAnnotation];
GO
CREATE PROCEDURE [dbo].[updateAnnotation]
(
@AnnotationID int,
@ItemID int,
@TypeID int,
@RtfText nvarchar(MAX)=null,
@SearchText nvarchar(MAX)=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Annotations]
SET
[ItemID]=@ItemID,
[TypeID]=@TypeID,
[RtfText]=@RtfText,
[SearchText]=@SearchText,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [AnnotationID]=@AnnotationID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Annotations] WHERE [AnnotationID]=@AnnotationID)
RAISERROR('Annotation record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Annotation has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Annotations] WHERE [AnnotationID]=@AnnotationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: updateAnnotation Error on Creation'
GO
/****** Object: StoredProcedure [addAnnotation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addAnnotation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addAnnotation];
GO
CREATE PROCEDURE [dbo].[addAnnotation]
(
@ItemID int,
@TypeID int,
@RtfText nvarchar(MAX)=null,
@SearchText nvarchar(MAX)=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newAnnotationID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Annotations]
(
[ItemID],
[TypeID],
[RtfText],
[SearchText],
[Config],
[DTS],
[UserID]
)
VALUES
(
@ItemID,
@TypeID,
@RtfText,
@SearchText,
@Config,
@DTS,
@UserID
)
SELECT @newAnnotationID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Annotations] WHERE [AnnotationID]=@newAnnotationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: addAnnotation Error on Creation'
GO
/****** Object: StoredProcedure [deleteAnnotation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteAnnotation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteAnnotation];
GO
CREATE PROCEDURE [dbo].[deleteAnnotation]
(
@AnnotationID int
)
AS
DELETE [Annotations]
WHERE [AnnotationID] = @AnnotationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: deleteAnnotation Error on Creation'
GO
/****** Object: StoredProcedure [getAnnotation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAnnotation];
GO
CREATE PROCEDURE [dbo].[getAnnotation]
(
@AnnotationID int
)
AS
SELECT
[AnnotationID],
[ItemID],
[TypeID],
[RtfText],
[SearchText],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Annotations]
WHERE [AnnotationID]=@AnnotationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotation Error on Creation'
GO
/****** Object: StoredProcedure [existsAnnotation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsAnnotation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsAnnotation];
GO
CREATE PROCEDURE [dbo].[existsAnnotation]
(
@AnnotationID int
)
AS
SELECT COUNT(*)
FROM [Annotations] WHERE [AnnotationID]=@AnnotationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsAnnotation Succeeded'
ELSE PRINT 'Procedure Creation: existsAnnotation Error on Creation'
GO
/****** Object: StoredProcedure [getAnnotationsByTypeID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationsByTypeID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAnnotationsByTypeID];
GO
CREATE PROCEDURE [dbo].[getAnnotationsByTypeID]
(
@TypeID int
)
AS
SELECT
[Annotations].[AnnotationID],
[Annotations].[ItemID],
[Annotations].[TypeID],
[Annotations].[RtfText],
[Annotations].[SearchText],
[Annotations].[Config],
[Annotations].[DTS],
[Annotations].[UserID],
[Annotations].[LastChanged],
[Items].[PreviousID] [Item_PreviousID],
[Items].[ContentID] [Item_ContentID],
[Items].[DTS] [Item_DTS],
[Items].[UserID] [Item_UserID]
FROM [Annotations]
JOIN [Items] ON
[Items].[ItemID]=[Annotations].[ItemID]
WHERE
[Annotations].[TypeID]=@TypeID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationsByTypeID Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationsByTypeID Error on Creation'
GO
/****** Object: StoredProcedure [getAnnotationsByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationsByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAnnotationsByItemID];
GO
CREATE PROCEDURE [dbo].[getAnnotationsByItemID]
(
@ItemID int
)
AS
SELECT
[Annotations].[AnnotationID],
[Annotations].[ItemID],
[Annotations].[TypeID],
[Annotations].[RtfText],
[Annotations].[SearchText],
[Annotations].[Config],
[Annotations].[DTS],
[Annotations].[UserID],
[Annotations].[LastChanged],
[AnnotationTypes].[Name] [AnnotationType_Name],
[AnnotationTypes].[Config] [AnnotationType_Config],
[AnnotationTypes].[DTS] [AnnotationType_DTS],
[AnnotationTypes].[UserID] [AnnotationType_UserID]
FROM [Annotations]
JOIN [AnnotationTypes] ON
[AnnotationTypes].[TypeID]=[Annotations].[TypeID]
WHERE
[Annotations].[ItemID]=@ItemID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationsByItemID Error on Creation'
GO
/****** Object: StoredProcedure [getAnnotationTypes] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationTypes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAnnotationTypes];
GO
CREATE PROCEDURE [dbo].[getAnnotationTypes]
AS
SELECT
[TypeID],
[Name],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[TypeID]=[AnnotationTypes].[TypeID]) [AnnotationCount]
FROM [AnnotationTypes]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationTypes Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationTypes Error on Creation'
GO
/****** Object: StoredProcedure [updateAnnotationType] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateAnnotationType]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateAnnotationType];
GO
CREATE PROCEDURE [dbo].[updateAnnotationType]
(
@TypeID int,
@Name nvarchar(100),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [AnnotationTypes]
SET
[Name]=@Name,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [TypeID]=@TypeID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [AnnotationTypes] WHERE [TypeID]=@TypeID)
RAISERROR('AnnotationType record has been deleted by another user', 16, 1)
ELSE
RAISERROR('AnnotationType has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [AnnotationTypes] WHERE [TypeID]=@TypeID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateAnnotationType Succeeded'
ELSE PRINT 'Procedure Creation: updateAnnotationType Error on Creation'
GO
/****** Object: StoredProcedure [addAnnotationType] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addAnnotationType]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addAnnotationType];
GO
CREATE PROCEDURE [dbo].[addAnnotationType]
(
@Name nvarchar(100),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newTypeID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [AnnotationTypes]
(
[Name],
[Config],
[DTS],
[UserID]
)
VALUES
(
@Name,
@Config,
@DTS,
@UserID
)
SELECT @newTypeID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [AnnotationTypes] WHERE [TypeID]=@newTypeID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addAnnotationType Succeeded'
ELSE PRINT 'Procedure Creation: addAnnotationType Error on Creation'
GO
/****** Object: StoredProcedure [deleteAnnotationType] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteAnnotationType]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteAnnotationType];
GO
CREATE PROCEDURE [dbo].[deleteAnnotationType]
(
@TypeID int
)
AS
DELETE [Annotations]
WHERE [TypeID]=@TypeID
DELETE [AnnotationTypes]
WHERE [TypeID] = @TypeID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteAnnotationType Succeeded'
ELSE PRINT 'Procedure Creation: deleteAnnotationType Error on Creation'
GO
/****** Object: StoredProcedure [getAnnotationType] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationType]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAnnotationType];
GO
CREATE PROCEDURE [dbo].[getAnnotationType]
(
@TypeID int
)
AS
SELECT
[TypeID],
[Name],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[TypeID]=[AnnotationTypes].[TypeID]) [AnnotationCount]
FROM [AnnotationTypes]
WHERE [TypeID]=@TypeID
SELECT
[Annotations].[AnnotationID],
[Annotations].[ItemID],
[Annotations].[TypeID],
[Annotations].[RtfText],
[Annotations].[SearchText],
[Annotations].[Config],
[Annotations].[DTS],
[Annotations].[UserID],
[Annotations].[LastChanged],
[Items].[PreviousID] [Item_PreviousID],
[Items].[ContentID] [Item_ContentID],
[Items].[DTS] [Item_DTS],
[Items].[UserID] [Item_UserID]
FROM [Annotations]
JOIN [Items] ON
[Items].[ItemID]=[Annotations].[ItemID]
WHERE
[Annotations].[TypeID]=@TypeID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationType Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationType Error on Creation'
GO
/****** Object: StoredProcedure [getAnnotationTypeByName] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAnnotationTypeByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAnnotationTypeByName];
GO
CREATE PROCEDURE [dbo].[getAnnotationTypeByName]
(
@Name nvarchar(100)
)
AS
SELECT
[TypeID],
[Name],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[TypeID]=[AnnotationTypes].[TypeID]) [AnnotationCount]
FROM [AnnotationTypes]
WHERE [Name]=@Name
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAnnotationTypeByName Succeeded'
ELSE PRINT 'Procedure Creation: getAnnotationTypeByName Error on Creation'
GO
/****** Object: StoredProcedure [existsAnnotationType] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsAnnotationType]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsAnnotationType];
GO
CREATE PROCEDURE [dbo].[existsAnnotationType]
(
@TypeID int
)
AS
SELECT COUNT(*)
FROM [AnnotationTypes] WHERE [TypeID]=@TypeID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsAnnotationType Succeeded'
ELSE PRINT 'Procedure Creation: existsAnnotationType Error on Creation'
GO
/****** Object: StoredProcedure [getAssignments] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssignments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssignments];
GO
CREATE PROCEDURE [dbo].[getAssignments]
AS
SELECT
[AID],
[GID],
[RID],
[FolderID],
[StartDate],
[EndDate],
[DTS],
[UsrID],
[LastChanged]
FROM [Assignments]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssignments Succeeded'
ELSE PRINT 'Procedure Creation: getAssignments Error on Creation'
GO
/****** Object: StoredProcedure [updateAssignment] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateAssignment]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateAssignment];
GO
CREATE PROCEDURE [dbo].[updateAssignment]
(
@AID int,
@GID int,
@RID int,
@FolderID int,
@StartDate datetime,
@EndDate datetime=null,
@DTS datetime,
@UsrID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Assignments]
SET
[GID]=@GID,
[RID]=@RID,
[FolderID]=@FolderID,
[StartDate]=@StartDate,
[EndDate]=@EndDate,
[DTS]=@DTS,
[UsrID]=@UsrID
WHERE [AID]=@AID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Assignments] WHERE [AID]=@AID)
RAISERROR('Assignment record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Assignment has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Assignments] WHERE [AID]=@AID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateAssignment Succeeded'
ELSE PRINT 'Procedure Creation: updateAssignment Error on Creation'
GO
/****** Object: StoredProcedure [addAssignment] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addAssignment]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addAssignment];
GO
CREATE PROCEDURE [dbo].[addAssignment]
(
@GID int,
@RID int,
@FolderID int,
@StartDate datetime,
@EndDate datetime=null,
@DTS datetime,
@UsrID nvarchar(100),
@newAID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Assignments]
(
[GID],
[RID],
[FolderID],
[StartDate],
[EndDate],
[DTS],
[UsrID]
)
VALUES
(
@GID,
@RID,
@FolderID,
@StartDate,
@EndDate,
@DTS,
@UsrID
)
SELECT @newAID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Assignments] WHERE [AID]=@newAID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addAssignment Succeeded'
ELSE PRINT 'Procedure Creation: addAssignment Error on Creation'
GO
/****** Object: StoredProcedure [deleteAssignment] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteAssignment]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteAssignment];
GO
CREATE PROCEDURE [dbo].[deleteAssignment]
(
@AID int
)
AS
DELETE [Assignments]
WHERE [AID] = @AID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteAssignment Succeeded'
ELSE PRINT 'Procedure Creation: deleteAssignment Error on Creation'
GO
/****** Object: StoredProcedure [getAssignment] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssignment]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssignment];
GO
CREATE PROCEDURE [dbo].[getAssignment]
(
@AID int
)
AS
SELECT
[AID],
[GID],
[RID],
[FolderID],
[StartDate],
[EndDate],
[DTS],
[UsrID],
[LastChanged]
FROM [Assignments]
WHERE [AID]=@AID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssignment Succeeded'
ELSE PRINT 'Procedure Creation: getAssignment Error on Creation'
GO
/****** Object: StoredProcedure [existsAssignment] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsAssignment]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsAssignment];
GO
CREATE PROCEDURE [dbo].[existsAssignment]
(
@AID int
)
AS
SELECT COUNT(*)
FROM [Assignments] WHERE [AID]=@AID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsAssignment Succeeded'
ELSE PRINT 'Procedure Creation: existsAssignment Error on Creation'
GO
/****** Object: StoredProcedure [getAssignmentsByFolderID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssignmentsByFolderID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssignmentsByFolderID];
GO
CREATE PROCEDURE [dbo].[getAssignmentsByFolderID]
(
@FolderID int
)
AS
SELECT
[Assignments].[AID],
[Assignments].[GID],
[Assignments].[RID],
[Assignments].[FolderID],
[Assignments].[StartDate],
[Assignments].[EndDate],
[Assignments].[DTS],
[Assignments].[UsrID],
[Assignments].[LastChanged],
[Groups].[GroupName] [Group_GroupName],
[Groups].[GroupType] [Group_GroupType],
[Groups].[Config] [Group_Config],
[Groups].[DTS] [Group_DTS],
[Groups].[UsrID] [Group_UsrID],
[Roles].[Name] [Role_Name],
[Roles].[Title] [Role_Title],
[Roles].[DTS] [Role_DTS],
[Roles].[UsrID] [Role_UsrID]
FROM [Assignments]
JOIN [Groups] ON
[Groups].[GID]=[Assignments].[GID]
JOIN [Roles] ON
[Roles].[RID]=[Assignments].[RID]
WHERE
[Assignments].[FolderID]=@FolderID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssignmentsByFolderID Succeeded'
ELSE PRINT 'Procedure Creation: getAssignmentsByFolderID Error on Creation'
GO
/****** Object: StoredProcedure [getAssignmentsByGID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssignmentsByGID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssignmentsByGID];
GO
CREATE PROCEDURE [dbo].[getAssignmentsByGID]
(
@GID int
)
AS
SELECT
[Assignments].[AID],
[Assignments].[GID],
[Assignments].[RID],
[Assignments].[FolderID],
[Assignments].[StartDate],
[Assignments].[EndDate],
[Assignments].[DTS],
[Assignments].[UsrID],
[Assignments].[LastChanged],
[Folders].[ParentID] [Folder_ParentID],
[Folders].[DBID] [Folder_DBID],
[Folders].[Name] [Folder_Name],
[Folders].[Title] [Folder_Title],
[Folders].[ShortName] [Folder_ShortName],
[Folders].[FormatID] [Folder_FormatID],
[Folders].[ManualOrder] [Folder_ManualOrder],
[Folders].[Config] [Folder_Config],
[Folders].[DTS] [Folder_DTS],
[Folders].[UsrID] [Folder_UsrID],
[Roles].[Name] [Role_Name],
[Roles].[Title] [Role_Title],
[Roles].[DTS] [Role_DTS],
[Roles].[UsrID] [Role_UsrID]
FROM [Assignments]
JOIN [Folders] ON
[Folders].[FolderID]=[Assignments].[FolderID]
JOIN [Roles] ON
[Roles].[RID]=[Assignments].[RID]
WHERE
[Assignments].[GID]=@GID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssignmentsByGID Succeeded'
ELSE PRINT 'Procedure Creation: getAssignmentsByGID Error on Creation'
GO
/****** Object: StoredProcedure [getAssignmentsByRID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssignmentsByRID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssignmentsByRID];
GO
CREATE PROCEDURE [dbo].[getAssignmentsByRID]
(
@RID int
)
AS
SELECT
[Assignments].[AID],
[Assignments].[GID],
[Assignments].[RID],
[Assignments].[FolderID],
[Assignments].[StartDate],
[Assignments].[EndDate],
[Assignments].[DTS],
[Assignments].[UsrID],
[Assignments].[LastChanged],
[Folders].[ParentID] [Folder_ParentID],
[Folders].[DBID] [Folder_DBID],
[Folders].[Name] [Folder_Name],
[Folders].[Title] [Folder_Title],
[Folders].[ShortName] [Folder_ShortName],
[Folders].[FormatID] [Folder_FormatID],
[Folders].[ManualOrder] [Folder_ManualOrder],
[Folders].[Config] [Folder_Config],
[Folders].[DTS] [Folder_DTS],
[Folders].[UsrID] [Folder_UsrID],
[Groups].[GroupName] [Group_GroupName],
[Groups].[GroupType] [Group_GroupType],
[Groups].[Config] [Group_Config],
[Groups].[DTS] [Group_DTS],
[Groups].[UsrID] [Group_UsrID]
FROM [Assignments]
JOIN [Folders] ON
[Folders].[FolderID]=[Assignments].[FolderID]
JOIN [Groups] ON
[Groups].[GID]=[Assignments].[GID]
WHERE
[Assignments].[RID]=@RID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssignmentsByRID Succeeded'
ELSE PRINT 'Procedure Creation: getAssignmentsByRID Error on Creation'
GO
/****** Object: StoredProcedure [getAssociations] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssociations]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssociations];
GO
CREATE PROCEDURE [dbo].[getAssociations]
AS
SELECT
[AssociationID],
[VersionID],
[ROFstID],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Associations]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssociations Succeeded'
ELSE PRINT 'Procedure Creation: getAssociations Error on Creation'
GO
/****** Object: StoredProcedure [updateAssociation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateAssociation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateAssociation];
GO
CREATE PROCEDURE [dbo].[updateAssociation]
(
@AssociationID int,
@VersionID int,
@ROFstID int,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Associations]
SET
[VersionID]=@VersionID,
[ROFstID]=@ROFstID,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [AssociationID]=@AssociationID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Associations] WHERE [AssociationID]=@AssociationID)
RAISERROR('Association record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Association has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Associations] WHERE [AssociationID]=@AssociationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateAssociation Succeeded'
ELSE PRINT 'Procedure Creation: updateAssociation Error on Creation'
GO
/****** Object: StoredProcedure [addAssociation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addAssociation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addAssociation];
GO
CREATE PROCEDURE [dbo].[addAssociation]
(
@VersionID int,
@ROFstID int,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newAssociationID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Associations]
(
[VersionID],
[ROFstID],
[Config],
[DTS],
[UserID]
)
VALUES
(
@VersionID,
@ROFstID,
@Config,
@DTS,
@UserID
)
SELECT @newAssociationID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Associations] WHERE [AssociationID]=@newAssociationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addAssociation Succeeded'
ELSE PRINT 'Procedure Creation: addAssociation Error on Creation'
GO
/****** Object: StoredProcedure [deleteAssociation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteAssociation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteAssociation];
GO
CREATE PROCEDURE [dbo].[deleteAssociation]
(
@AssociationID int
)
AS
DELETE [Associations]
WHERE [AssociationID] = @AssociationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteAssociation Succeeded'
ELSE PRINT 'Procedure Creation: deleteAssociation Error on Creation'
GO
/****** Object: StoredProcedure [getAssociation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssociation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssociation];
GO
CREATE PROCEDURE [dbo].[getAssociation]
(
@AssociationID int
)
AS
SELECT
[AssociationID],
[VersionID],
[ROFstID],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Associations]
WHERE [AssociationID]=@AssociationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssociation Succeeded'
ELSE PRINT 'Procedure Creation: getAssociation Error on Creation'
GO
/****** Object: StoredProcedure [getAssociationByVersionID_ROFstID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssociationByVersionID_ROFstID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssociationByVersionID_ROFstID];
GO
CREATE PROCEDURE [dbo].[getAssociationByVersionID_ROFstID]
(
@VersionID int,
@ROFstID int
)
AS
SELECT
[AssociationID],
[VersionID],
[ROFstID],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Associations]
WHERE [VersionID]=@VersionID AND [ROFstID]=@ROFstID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssociationByVersionID_ROFstID Succeeded'
ELSE PRINT 'Procedure Creation: getAssociationByVersionID_ROFstID Error on Creation'
GO
/****** Object: StoredProcedure [existsAssociation] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsAssociation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsAssociation];
GO
CREATE PROCEDURE [dbo].[existsAssociation]
(
@AssociationID int
)
AS
SELECT COUNT(*)
FROM [Associations] WHERE [AssociationID]=@AssociationID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsAssociation Succeeded'
ELSE PRINT 'Procedure Creation: existsAssociation Error on Creation'
GO
/****** Object: StoredProcedure [getAssociationsByVersionID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssociationsByVersionID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssociationsByVersionID];
GO
CREATE PROCEDURE [dbo].[getAssociationsByVersionID]
(
@VersionID int
)
AS
SELECT
[Associations].[AssociationID],
[Associations].[VersionID],
[Associations].[ROFstID],
[Associations].[Config],
[Associations].[DTS],
[Associations].[UserID],
[Associations].[LastChanged],
[ROFsts].[RODbID] [ROFst_RODbID],
[ROFsts].[ROLookup] [ROFst_ROLookup],
[ROFsts].[Config] [ROFst_Config],
[ROFsts].[DTS] [ROFst_DTS],
[ROFsts].[UserID] [ROFst_UserID]
FROM [Associations]
JOIN [ROFsts] ON
[ROFsts].[ROFstID]=[Associations].[ROFstID]
WHERE
[Associations].[VersionID]=@VersionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssociationsByVersionID Succeeded'
ELSE PRINT 'Procedure Creation: getAssociationsByVersionID Error on Creation'
GO
/****** Object: StoredProcedure [getAssociationsByROFstID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getAssociationsByROFstID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getAssociationsByROFstID];
GO
CREATE PROCEDURE [dbo].[getAssociationsByROFstID]
(
@ROFstID int
)
AS
SELECT
[Associations].[AssociationID],
[Associations].[VersionID],
[Associations].[ROFstID],
[Associations].[Config],
[Associations].[DTS],
[Associations].[UserID],
[Associations].[LastChanged],
[DocVersions].[FolderID] [DocVersion_FolderID],
[DocVersions].[VersionType] [DocVersion_VersionType],
[DocVersions].[Name] [DocVersion_Name],
[DocVersions].[Title] [DocVersion_Title],
[DocVersions].[ItemID] [DocVersion_ItemID],
[DocVersions].[FormatID] [DocVersion_FormatID],
[DocVersions].[Config] [DocVersion_Config],
[DocVersions].[DTS] [DocVersion_DTS],
[DocVersions].[UserID] [DocVersion_UserID]
FROM [Associations]
JOIN [DocVersions] ON
[DocVersions].[VersionID]=[Associations].[VersionID]
WHERE
[Associations].[ROFstID]=@ROFstID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getAssociationsByROFstID Succeeded'
ELSE PRINT 'Procedure Creation: getAssociationsByROFstID Error on Creation'
GO
/****** Object: StoredProcedure [getConnections] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getConnections]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getConnections];
GO
CREATE PROCEDURE [dbo].[getConnections]
AS
SELECT
[DBID],
[Name],
[Title],
[ConnectionString],
[ServerType],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[DBID]=[Connections].[DBID]) [FolderCount]
FROM [Connections]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getConnections Succeeded'
ELSE PRINT 'Procedure Creation: getConnections Error on Creation'
GO
/****** Object: StoredProcedure [updateConnection] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateConnection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateConnection];
GO
CREATE PROCEDURE [dbo].[updateConnection]
(
@DBID int,
@Name nvarchar(100)=null,
@Title nvarchar(510)=null,
@ConnectionString nvarchar(510)=null,
@ServerType int,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Connections]
SET
[Name]=@Name,
[Title]=@Title,
[ConnectionString]=@ConnectionString,
[ServerType]=@ServerType,
[Config]=@Config,
[DTS]=@DTS,
[UsrID]=@UsrID
WHERE [DBID]=@DBID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Connections] WHERE [DBID]=@DBID)
RAISERROR('Connection record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Connection has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Connections] WHERE [DBID]=@DBID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateConnection Succeeded'
ELSE PRINT 'Procedure Creation: updateConnection Error on Creation'
GO
/****** Object: StoredProcedure [addConnection] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addConnection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addConnection];
GO
CREATE PROCEDURE [dbo].[addConnection]
(
@Name nvarchar(100)=null,
@Title nvarchar(510)=null,
@ConnectionString nvarchar(510)=null,
@ServerType int,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@newDBID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Connections]
(
[Name],
[Title],
[ConnectionString],
[ServerType],
[Config],
[DTS],
[UsrID]
)
VALUES
(
@Name,
@Title,
@ConnectionString,
@ServerType,
@Config,
@DTS,
@UsrID
)
SELECT @newDBID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Connections] WHERE [DBID]=@newDBID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addConnection Succeeded'
ELSE PRINT 'Procedure Creation: addConnection Error on Creation'
GO
/****** Object: StoredProcedure [deleteConnection] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteConnection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteConnection];
GO
CREATE PROCEDURE [dbo].[deleteConnection]
(
@DBID int
)
AS
DELETE [Folders]
WHERE [DBID]=@DBID
DELETE [Connections]
WHERE [DBID] = @DBID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteConnection Succeeded'
ELSE PRINT 'Procedure Creation: deleteConnection Error on Creation'
GO
/****** Object: StoredProcedure [getConnection] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getConnection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getConnection];
GO
CREATE PROCEDURE [dbo].[getConnection]
(
@DBID int
)
AS
SELECT
[DBID],
[Name],
[Title],
[ConnectionString],
[ServerType],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[DBID]=[Connections].[DBID]) [FolderCount]
FROM [Connections]
WHERE [DBID]=@DBID
SELECT
[Folders].[FolderID],
[Folders].[ParentID],
[Folders].[DBID],
[Folders].[Name],
[Folders].[Title],
[Folders].[ShortName],
[Folders].[FormatID],
[Folders].[ManualOrder],
[Folders].[Config],
[Folders].[DTS],
[Folders].[UsrID],
[Folders].[LastChanged]
FROM [Folders]
WHERE
[Folders].[DBID]=@DBID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getConnection Succeeded'
ELSE PRINT 'Procedure Creation: getConnection Error on Creation'
GO
/****** Object: StoredProcedure [getConnectionByName] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getConnectionByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getConnectionByName];
GO
CREATE PROCEDURE [dbo].[getConnectionByName]
(
@Name nvarchar(100)
)
AS
SELECT
[DBID],
[Name],
[Title],
[ConnectionString],
[ServerType],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[DBID]=[Connections].[DBID]) [FolderCount]
FROM [Connections]
WHERE [Name]=@Name
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getConnectionByName Succeeded'
ELSE PRINT 'Procedure Creation: getConnectionByName Error on Creation'
GO
/****** Object: StoredProcedure [existsConnection] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsConnection]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsConnection];
GO
CREATE PROCEDURE [dbo].[existsConnection]
(
@DBID int
)
AS
SELECT COUNT(*)
FROM [Connections] WHERE [DBID]=@DBID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsConnection Succeeded'
ELSE PRINT 'Procedure Creation: existsConnection Error on Creation'
GO
/****** Object: StoredProcedure [getContents] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContents]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getContents];
GO
CREATE PROCEDURE [dbo].[getContents]
AS
SELECT
[ContentID],
[Number],
[Text],
[Type],
[FormatID],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[Contents].[ContentID]) [DetailCount],
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[Contents].[ContentID]) [EntryCount],
(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[Contents].[ContentID]) [ItemCount],
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[Contents].[ContentID]) [PartCount],
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[Contents].[ContentID]) [RoUsageCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[Contents].[ContentID]) [TransitionCount],
(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[Contents].[ContentID]) [ZContentCount]
FROM [Contents]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContents Succeeded'
ELSE PRINT 'Procedure Creation: getContents Error on Creation'
GO
/****** Object: StoredProcedure [updateContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateContent];
GO
CREATE PROCEDURE [dbo].[updateContent]
(
@ContentID int,
@Number nvarchar(30)=null,
@Text nvarchar(MAX)=null,
@Type int=null,
@FormatID int=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Contents]
SET
[Number]=@Number,
[Text]=@Text,
[Type]=@Type,
[FormatID]=@FormatID,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [ContentID]=@ContentID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Contents] WHERE [ContentID]=@ContentID)
RAISERROR('Content record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Content has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Contents] WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateContent Succeeded'
ELSE PRINT 'Procedure Creation: updateContent Error on Creation'
GO
/****** Object: StoredProcedure [addContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addContent];
GO
CREATE PROCEDURE [dbo].[addContent]
(
@Number nvarchar(30)=null,
@Text nvarchar(MAX)=null,
@Type int=null,
@FormatID int=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newContentID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Contents]
(
[Number],
[Text],
[Type],
[FormatID],
[Config],
[DTS],
[UserID]
)
VALUES
(
@Number,
@Text,
@Type,
@FormatID,
@Config,
@DTS,
@UserID
)
SELECT @newContentID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Contents] WHERE [ContentID]=@newContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addContent Succeeded'
ELSE PRINT 'Procedure Creation: addContent Error on Creation'
GO
/****** Object: StoredProcedure [deleteContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteContent];
GO
CREATE PROCEDURE [dbo].[deleteContent]
(
@ContentID int
)
AS
DELETE [Details]
WHERE [ContentID]=@ContentID
DELETE [Entries]
WHERE [ContentID]=@ContentID
DELETE [Items]
WHERE [ContentID]=@ContentID
DELETE [Parts]
WHERE [ContentID]=@ContentID
DELETE [RoUsages]
WHERE [ContentID]=@ContentID
DELETE [Transitions]
WHERE [FromID]=@ContentID
DELETE [ZContents]
WHERE [ContentID]=@ContentID
DELETE [Contents]
WHERE [ContentID] = @ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteContent Succeeded'
ELSE PRINT 'Procedure Creation: deleteContent Error on Creation'
GO
/****** Object: StoredProcedure [getContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getContent];
GO
CREATE PROCEDURE [dbo].[getContent]
(
@ContentID int
)
AS
SELECT
[ContentID],
[Number],
[Text],
[Type],
[FormatID],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[Contents].[ContentID]) [DetailCount],
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[Contents].[ContentID]) [EntryCount],
(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[Contents].[ContentID]) [ItemCount],
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[Contents].[ContentID]) [PartCount],
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[Contents].[ContentID]) [RoUsageCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[Contents].[ContentID]) [TransitionCount],
(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[Contents].[ContentID]) [ZContentCount]
FROM [Contents]
WHERE [ContentID]=@ContentID
SELECT
[Details].[DetailID],
[Details].[ContentID],
[Details].[ItemType],
[Details].[Text],
[Details].[Config],
[Details].[DTS],
[Details].[UserID],
[Details].[LastChanged]
FROM [Details]
WHERE
[Details].[ContentID]=@ContentID
SELECT
[Entries].[ContentID],
[Entries].[DocID],
[Entries].[DTS],
[Entries].[UserID],
[Entries].[LastChanged],
[Documents].[LibTitle] [Document_LibTitle],
[Documents].[DocContent] [Document_DocContent],
[Documents].[DocAscii] [Document_DocAscii],
[Documents].[FileExtension] [Document_FileExtension],
[Documents].[Config] [Document_Config],
[Documents].[DTS] [Document_DTS],
[Documents].[UserID] [Document_UserID]
FROM [Entries]
JOIN [Documents] ON
[Documents].[DocID]=[Entries].[DocID]
WHERE
[Entries].[ContentID]=@ContentID
SELECT
[Items].[ItemID],
[Items].[PreviousID],
[Items].[ContentID],
[Items].[DTS],
[Items].[UserID],
[Items].[LastChanged]
FROM [Items]
WHERE
[Items].[ContentID]=@ContentID
SELECT
[Parts].[ContentID],
[Parts].[FromType],
[Parts].[ItemID],
[Parts].[DTS],
[Parts].[UserID],
[Parts].[LastChanged],
[Items].[PreviousID] [Item_PreviousID],
[Items].[ContentID] [Item_ContentID],
[Items].[DTS] [Item_DTS],
[Items].[UserID] [Item_UserID]
FROM [Parts]
JOIN [Items] ON
[Items].[ItemID]=[Parts].[ItemID]
WHERE
[Parts].[ContentID]=@ContentID
SELECT
[RoUsages].[ROUsageID],
[RoUsages].[ContentID],
[RoUsages].[ROID],
[RoUsages].[Config],
[RoUsages].[DTS],
[RoUsages].[UserID],
[RoUsages].[LastChanged],
[RoUsages].[RODbID],
[RODbs].[ROName] [RODb_ROName],
[RODbs].[FolderPath] [RODb_FolderPath],
[RODbs].[DBConnectionString] [RODb_DBConnectionString],
[RODbs].[Config] [RODb_Config],
[RODbs].[DTS] [RODb_DTS],
[RODbs].[UserID] [RODb_UserID]
FROM [RoUsages]
JOIN [RODbs] ON
[RODbs].[RODbID]=[RoUsages].[RODbID]
WHERE
[RoUsages].[ContentID]=@ContentID
SELECT
[Transitions].[TransitionID],
[Transitions].[FromID],
[Transitions].[ToID],
[Transitions].[RangeID],
[Transitions].[TranType],
[Transitions].[Config],
[Transitions].[DTS],
[Transitions].[UserID],
[Transitions].[LastChanged],
[Items_RangeID].[PreviousID] [Item_RangeID_PreviousID],
[Items_RangeID].[ContentID] [Item_RangeID_ContentID],
[Items_RangeID].[DTS] [Item_RangeID_DTS],
[Items_RangeID].[UserID] [Item_RangeID_UserID],
[Items_ToID].[PreviousID] [Item_ToID_PreviousID],
[Items_ToID].[ContentID] [Item_ToID_ContentID],
[Items_ToID].[DTS] [Item_ToID_DTS],
[Items_ToID].[UserID] [Item_ToID_UserID]
FROM [Transitions]
JOIN [Items] [Items_RangeID] ON
[Items_RangeID].[ItemID]=[Transitions].[RangeID]
JOIN [Items] [Items_ToID] ON
[Items_ToID].[ItemID]=[Transitions].[ToID]
WHERE
[Transitions].[FromID]=@ContentID
SELECT
[ZContents].[ContentID],
[ZContents].[OldStepSequence],
[ZContents].[LastChanged]
FROM [ZContents]
WHERE
[ZContents].[ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContent Succeeded'
ELSE PRINT 'Procedure Creation: getContent Error on Creation'
GO
/****** Object: StoredProcedure [existsContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsContent];
GO
CREATE PROCEDURE [dbo].[existsContent]
(
@ContentID int
)
AS
SELECT COUNT(*)
FROM [Contents] WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsContent Succeeded'
ELSE PRINT 'Procedure Creation: existsContent Error on Creation'
GO
/****** Object: StoredProcedure [getContentsByFormatID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getContentsByFormatID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getContentsByFormatID];
GO
CREATE PROCEDURE [dbo].[getContentsByFormatID]
(
@FormatID int
)
AS
SELECT
[Contents].[ContentID],
[Contents].[Number],
[Contents].[Text],
[Contents].[Type],
[Contents].[FormatID],
[Contents].[Config],
[Contents].[DTS],
[Contents].[UserID],
[Contents].[LastChanged],
(SELECT COUNT(*) FROM [Details] WHERE [Details].[ContentID]=[Contents].[ContentID]) [DetailCount],
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[ContentID]=[Contents].[ContentID]) [EntryCount],
(SELECT COUNT(*) FROM [Items] WHERE [Items].[ContentID]=[Contents].[ContentID]) [ItemCount],
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ContentID]=[Contents].[ContentID]) [PartCount],
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[ContentID]=[Contents].[ContentID]) [RoUsageCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[FromID]=[Contents].[ContentID]) [TransitionCount],
(SELECT COUNT(*) FROM [ZContents] WHERE [ZContents].[ContentID]=[Contents].[ContentID]) [ZContentCount]
FROM [Contents]
WHERE
[Contents].[FormatID]=@FormatID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getContentsByFormatID Succeeded'
ELSE PRINT 'Procedure Creation: getContentsByFormatID Error on Creation'
GO
/****** Object: StoredProcedure [getDetails] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDetails]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDetails];
GO
CREATE PROCEDURE [dbo].[getDetails]
AS
SELECT
[DetailID],
[ContentID],
[ItemType],
[Text],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Details]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDetails Succeeded'
ELSE PRINT 'Procedure Creation: getDetails Error on Creation'
GO
/****** Object: StoredProcedure [updateDetail] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateDetail]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateDetail];
GO
CREATE PROCEDURE [dbo].[updateDetail]
(
@DetailID int,
@ContentID int,
@ItemType int,
@Text nvarchar(MAX),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Details]
SET
[ContentID]=@ContentID,
[ItemType]=@ItemType,
[Text]=@Text,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [DetailID]=@DetailID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Details] WHERE [DetailID]=@DetailID)
RAISERROR('Detail record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Detail has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Details] WHERE [DetailID]=@DetailID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateDetail Succeeded'
ELSE PRINT 'Procedure Creation: updateDetail Error on Creation'
GO
/****** Object: StoredProcedure [addDetail] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addDetail]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addDetail];
GO
CREATE PROCEDURE [dbo].[addDetail]
(
@ContentID int,
@ItemType int,
@Text nvarchar(MAX),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newDetailID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Details]
(
[ContentID],
[ItemType],
[Text],
[Config],
[DTS],
[UserID]
)
VALUES
(
@ContentID,
@ItemType,
@Text,
@Config,
@DTS,
@UserID
)
SELECT @newDetailID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Details] WHERE [DetailID]=@newDetailID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addDetail Succeeded'
ELSE PRINT 'Procedure Creation: addDetail Error on Creation'
GO
/****** Object: StoredProcedure [deleteDetail] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteDetail]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteDetail];
GO
CREATE PROCEDURE [dbo].[deleteDetail]
(
@DetailID int
)
AS
DELETE [Details]
WHERE [DetailID] = @DetailID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteDetail Succeeded'
ELSE PRINT 'Procedure Creation: deleteDetail Error on Creation'
GO
/****** Object: StoredProcedure [getDetail] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDetail]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDetail];
GO
CREATE PROCEDURE [dbo].[getDetail]
(
@DetailID int
)
AS
SELECT
[DetailID],
[ContentID],
[ItemType],
[Text],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Details]
WHERE [DetailID]=@DetailID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDetail Succeeded'
ELSE PRINT 'Procedure Creation: getDetail Error on Creation'
GO
/****** Object: StoredProcedure [existsDetail] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsDetail]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsDetail];
GO
CREATE PROCEDURE [dbo].[existsDetail]
(
@DetailID int
)
AS
SELECT COUNT(*)
FROM [Details] WHERE [DetailID]=@DetailID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsDetail Succeeded'
ELSE PRINT 'Procedure Creation: existsDetail Error on Creation'
GO
/****** Object: StoredProcedure [getDetailsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDetailsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDetailsByContentID];
GO
CREATE PROCEDURE [dbo].[getDetailsByContentID]
(
@ContentID int
)
AS
SELECT
[Details].[DetailID],
[Details].[ContentID],
[Details].[ItemType],
[Details].[Text],
[Details].[Config],
[Details].[DTS],
[Details].[UserID],
[Details].[LastChanged]
FROM [Details]
WHERE
[Details].[ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDetailsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getDetailsByContentID Error on Creation'
GO
/****** Object: StoredProcedure [getDocuments] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocuments]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDocuments];
GO
CREATE PROCEDURE [dbo].[getDocuments]
AS
SELECT
[DocID],
[LibTitle],
[DocContent],
[DocAscii],
[FileExtension],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[Documents].[DocID]) [EntryCount]
FROM [Documents]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocuments Succeeded'
ELSE PRINT 'Procedure Creation: getDocuments Error on Creation'
GO
/****** Object: StoredProcedure [updateDocument] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateDocument]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateDocument];
GO
CREATE PROCEDURE [dbo].[updateDocument]
(
@DocID int,
@LibTitle nvarchar(1024)=null,
@DocContent varbinary(MAX)=null,
@DocAscii nvarchar(MAX)=null,
@FileExtension nvarchar(10),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Documents]
SET
[LibTitle]=@LibTitle,
[DocContent]=@DocContent,
[DocAscii]=@DocAscii,
[FileExtension]=@FileExtension,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [DocID]=@DocID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Documents] WHERE [DocID]=@DocID)
RAISERROR('Document record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Document has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Documents] WHERE [DocID]=@DocID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateDocument Succeeded'
ELSE PRINT 'Procedure Creation: updateDocument Error on Creation'
GO
/****** Object: StoredProcedure [addDocument] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addDocument]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addDocument];
GO
CREATE PROCEDURE [dbo].[addDocument]
(
@LibTitle nvarchar(1024)=null,
@DocContent varbinary(MAX)=null,
@DocAscii nvarchar(MAX)=null,
@FileExtension nvarchar(10),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newDocID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Documents]
(
[LibTitle],
[DocContent],
[DocAscii],
[FileExtension],
[Config],
[DTS],
[UserID]
)
VALUES
(
@LibTitle,
@DocContent,
@DocAscii,
@FileExtension,
@Config,
@DTS,
@UserID
)
SELECT @newDocID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Documents] WHERE [DocID]=@newDocID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addDocument Succeeded'
ELSE PRINT 'Procedure Creation: addDocument Error on Creation'
GO
/****** Object: StoredProcedure [deleteDocument] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteDocument]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteDocument];
GO
CREATE PROCEDURE [dbo].[deleteDocument]
(
@DocID int
)
AS
DELETE [Entries]
WHERE [DocID]=@DocID
DELETE [Documents]
WHERE [DocID] = @DocID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteDocument Succeeded'
ELSE PRINT 'Procedure Creation: deleteDocument Error on Creation'
GO
/****** Object: StoredProcedure [getDocument] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocument]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDocument];
GO
CREATE PROCEDURE [dbo].[getDocument]
(
@DocID int
)
AS
SELECT
[DocID],
[LibTitle],
[DocContent],
[DocAscii],
[FileExtension],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Entries] WHERE [Entries].[DocID]=[Documents].[DocID]) [EntryCount]
FROM [Documents]
WHERE [DocID]=@DocID
SELECT
[Entries].[ContentID],
[Entries].[DocID],
[Entries].[DTS],
[Entries].[UserID],
[Entries].[LastChanged],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID]
FROM [Entries]
JOIN [Contents] ON
[Contents].[ContentID]=[Entries].[ContentID]
WHERE
[Entries].[DocID]=@DocID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocument Succeeded'
ELSE PRINT 'Procedure Creation: getDocument Error on Creation'
GO
/****** Object: StoredProcedure [existsDocument] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsDocument]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsDocument];
GO
CREATE PROCEDURE [dbo].[existsDocument]
(
@DocID int
)
AS
SELECT COUNT(*)
FROM [Documents] WHERE [DocID]=@DocID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsDocument Succeeded'
ELSE PRINT 'Procedure Creation: existsDocument Error on Creation'
GO
/****** Object: StoredProcedure [getDocVersions] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocVersions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDocVersions];
GO
CREATE PROCEDURE [dbo].[getDocVersions]
AS
SELECT
[VersionID],
[FolderID],
[VersionType],
[Name],
[Title],
[ItemID],
[FormatID],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
FROM [DocVersions]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocVersions Succeeded'
ELSE PRINT 'Procedure Creation: getDocVersions Error on Creation'
GO
/****** Object: StoredProcedure [updateDocVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateDocVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateDocVersion];
GO
CREATE PROCEDURE [dbo].[updateDocVersion]
(
@VersionID int,
@FolderID int,
@VersionType int,
@Name nvarchar(100),
@Title nvarchar(510)=null,
@ItemID int=null,
@FormatID int=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [DocVersions]
SET
[FolderID]=@FolderID,
[VersionType]=@VersionType,
[Name]=@Name,
[Title]=@Title,
[ItemID]=@ItemID,
[FormatID]=@FormatID,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [VersionID]=@VersionID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [DocVersions] WHERE [VersionID]=@VersionID)
RAISERROR('DocVersion record has been deleted by another user', 16, 1)
ELSE
RAISERROR('DocVersion has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [DocVersions] WHERE [VersionID]=@VersionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateDocVersion Succeeded'
ELSE PRINT 'Procedure Creation: updateDocVersion Error on Creation'
GO
/****** Object: StoredProcedure [addDocVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addDocVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addDocVersion];
GO
CREATE PROCEDURE [dbo].[addDocVersion]
(
@FolderID int,
@VersionType int,
@Name nvarchar(100),
@Title nvarchar(510)=null,
@ItemID int=null,
@FormatID int=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newVersionID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [DocVersions]
(
[FolderID],
[VersionType],
[Name],
[Title],
[ItemID],
[FormatID],
[Config],
[DTS],
[UserID]
)
VALUES
(
@FolderID,
@VersionType,
@Name,
@Title,
@ItemID,
@FormatID,
@Config,
@DTS,
@UserID
)
SELECT @newVersionID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [DocVersions] WHERE [VersionID]=@newVersionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addDocVersion Succeeded'
ELSE PRINT 'Procedure Creation: addDocVersion Error on Creation'
GO
/****** Object: StoredProcedure [deleteDocVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteDocVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteDocVersion];
GO
CREATE PROCEDURE [dbo].[deleteDocVersion]
(
@VersionID int
)
AS
DELETE [Associations]
WHERE [VersionID]=@VersionID
DELETE [DocVersions]
WHERE [VersionID] = @VersionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteDocVersion Succeeded'
ELSE PRINT 'Procedure Creation: deleteDocVersion Error on Creation'
GO
/****** Object: StoredProcedure [getDocVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDocVersion];
GO
CREATE PROCEDURE [dbo].[getDocVersion]
(
@VersionID int
)
AS
SELECT
[VersionID],
[FolderID],
[VersionType],
[Name],
[Title],
[ItemID],
[FormatID],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
FROM [DocVersions]
WHERE [VersionID]=@VersionID
SELECT
[Associations].[AssociationID],
[Associations].[VersionID],
[Associations].[ROFstID],
[Associations].[Config],
[Associations].[DTS],
[Associations].[UserID],
[Associations].[LastChanged],
[ROFsts].[RODbID] [ROFst_RODbID],
[ROFsts].[ROLookup] [ROFst_ROLookup],
[ROFsts].[Config] [ROFst_Config],
[ROFsts].[DTS] [ROFst_DTS],
[ROFsts].[UserID] [ROFst_UserID]
FROM [Associations]
JOIN [ROFsts] ON
[ROFsts].[ROFstID]=[Associations].[ROFstID]
WHERE
[Associations].[VersionID]=@VersionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocVersion Succeeded'
ELSE PRINT 'Procedure Creation: getDocVersion Error on Creation'
GO
/****** Object: StoredProcedure [existsDocVersion] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsDocVersion]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsDocVersion];
GO
CREATE PROCEDURE [dbo].[existsDocVersion]
(
@VersionID int
)
AS
SELECT COUNT(*)
FROM [DocVersions] WHERE [VersionID]=@VersionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsDocVersion Succeeded'
ELSE PRINT 'Procedure Creation: existsDocVersion Error on Creation'
GO
/****** Object: StoredProcedure [getDocVersionsByFolderID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocVersionsByFolderID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDocVersionsByFolderID];
GO
CREATE PROCEDURE [dbo].[getDocVersionsByFolderID]
(
@FolderID int
)
AS
SELECT
[DocVersions].[VersionID],
[DocVersions].[FolderID],
[DocVersions].[VersionType],
[DocVersions].[Name],
[DocVersions].[Title],
[DocVersions].[ItemID],
[DocVersions].[FormatID],
[DocVersions].[Config],
[DocVersions].[DTS],
[DocVersions].[UserID],
[DocVersions].[LastChanged],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
FROM [DocVersions]
WHERE
[DocVersions].[FolderID]=@FolderID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocVersionsByFolderID Succeeded'
ELSE PRINT 'Procedure Creation: getDocVersionsByFolderID Error on Creation'
GO
/****** Object: StoredProcedure [getDocVersionsByFormatID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocVersionsByFormatID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDocVersionsByFormatID];
GO
CREATE PROCEDURE [dbo].[getDocVersionsByFormatID]
(
@FormatID int
)
AS
SELECT
[DocVersions].[VersionID],
[DocVersions].[FolderID],
[DocVersions].[VersionType],
[DocVersions].[Name],
[DocVersions].[Title],
[DocVersions].[ItemID],
[DocVersions].[FormatID],
[DocVersions].[Config],
[DocVersions].[DTS],
[DocVersions].[UserID],
[DocVersions].[LastChanged],
[Folders].[ParentID] [Folder_ParentID],
[Folders].[DBID] [Folder_DBID],
[Folders].[Name] [Folder_Name],
[Folders].[Title] [Folder_Title],
[Folders].[ShortName] [Folder_ShortName],
[Folders].[FormatID] [Folder_FormatID],
[Folders].[ManualOrder] [Folder_ManualOrder],
[Folders].[Config] [Folder_Config],
[Folders].[DTS] [Folder_DTS],
[Folders].[UsrID] [Folder_UsrID],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
FROM [DocVersions]
JOIN [Folders] ON
[Folders].[FolderID]=[DocVersions].[FolderID]
WHERE
[DocVersions].[FormatID]=@FormatID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocVersionsByFormatID Succeeded'
ELSE PRINT 'Procedure Creation: getDocVersionsByFormatID Error on Creation'
GO
/****** Object: StoredProcedure [getDocVersionsByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getDocVersionsByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getDocVersionsByItemID];
GO
CREATE PROCEDURE [dbo].[getDocVersionsByItemID]
(
@ItemID int
)
AS
SELECT
[DocVersions].[VersionID],
[DocVersions].[FolderID],
[DocVersions].[VersionType],
[DocVersions].[Name],
[DocVersions].[Title],
[DocVersions].[ItemID],
[DocVersions].[FormatID],
[DocVersions].[Config],
[DocVersions].[DTS],
[DocVersions].[UserID],
[DocVersions].[LastChanged],
[Folders].[ParentID] [Folder_ParentID],
[Folders].[DBID] [Folder_DBID],
[Folders].[Name] [Folder_Name],
[Folders].[Title] [Folder_Title],
[Folders].[ShortName] [Folder_ShortName],
[Folders].[FormatID] [Folder_FormatID],
[Folders].[ManualOrder] [Folder_ManualOrder],
[Folders].[Config] [Folder_Config],
[Folders].[DTS] [Folder_DTS],
[Folders].[UsrID] [Folder_UsrID],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[VersionID]=[DocVersions].[VersionID]) [AssociationCount]
FROM [DocVersions]
JOIN [Folders] ON
[Folders].[FolderID]=[DocVersions].[FolderID]
WHERE
[DocVersions].[ItemID]=@ItemID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getDocVersionsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getDocVersionsByItemID Error on Creation'
GO
/****** Object: StoredProcedure [getEntries] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getEntries]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getEntries];
GO
CREATE PROCEDURE [dbo].[getEntries]
AS
SELECT
[ContentID],
[DocID],
[DTS],
[UserID],
[LastChanged]
FROM [Entries]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntries Succeeded'
ELSE PRINT 'Procedure Creation: getEntries Error on Creation'
GO
/****** Object: StoredProcedure [updateEntry] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateEntry]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateEntry];
GO
CREATE PROCEDURE [dbo].[updateEntry]
(
@ContentID int,
@DocID int,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Entries]
SET
[DocID]=@DocID,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [ContentID]=@ContentID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Entries] WHERE [ContentID]=@ContentID)
RAISERROR('Entry record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Entry has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Entries] WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateEntry Succeeded'
ELSE PRINT 'Procedure Creation: updateEntry Error on Creation'
GO
/****** Object: StoredProcedure [addEntry] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addEntry]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addEntry];
GO
CREATE PROCEDURE [dbo].[addEntry]
(
@ContentID int,
@DocID int,
@DTS datetime,
@UserID nvarchar(100),
@newLastChanged timestamp output
)
AS
INSERT INTO [Entries]
(
[ContentID],
[DocID],
[DTS],
[UserID]
)
VALUES
(
@ContentID,
@DocID,
@DTS,
@UserID
)
SELECT @newLastChanged=[LastChanged]
FROM [Entries] WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addEntry Succeeded'
ELSE PRINT 'Procedure Creation: addEntry Error on Creation'
GO
/****** Object: StoredProcedure [deleteEntry] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteEntry]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteEntry];
GO
CREATE PROCEDURE [dbo].[deleteEntry]
(
@ContentID int
)
AS
DELETE [Entries]
WHERE [ContentID] = @ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteEntry Succeeded'
ELSE PRINT 'Procedure Creation: deleteEntry Error on Creation'
GO
/****** Object: StoredProcedure [getEntry] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getEntry]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getEntry];
GO
CREATE PROCEDURE [dbo].[getEntry]
(
@ContentID int
)
AS
SELECT
[ContentID],
[DocID],
[DTS],
[UserID],
[LastChanged]
FROM [Entries]
WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntry Succeeded'
ELSE PRINT 'Procedure Creation: getEntry Error on Creation'
GO
/****** Object: StoredProcedure [existsEntry] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsEntry]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsEntry];
GO
CREATE PROCEDURE [dbo].[existsEntry]
(
@ContentID int
)
AS
SELECT COUNT(*)
FROM [Entries] WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsEntry Succeeded'
ELSE PRINT 'Procedure Creation: existsEntry Error on Creation'
GO
/****** Object: StoredProcedure [getEntriesByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getEntriesByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getEntriesByContentID];
GO
CREATE PROCEDURE [dbo].[getEntriesByContentID]
(
@ContentID int
)
AS
SELECT
[Entries].[ContentID],
[Entries].[DocID],
[Entries].[DTS],
[Entries].[UserID],
[Entries].[LastChanged],
[Documents].[LibTitle] [Document_LibTitle],
[Documents].[DocContent] [Document_DocContent],
[Documents].[DocAscii] [Document_DocAscii],
[Documents].[FileExtension] [Document_FileExtension],
[Documents].[Config] [Document_Config],
[Documents].[DTS] [Document_DTS],
[Documents].[UserID] [Document_UserID]
FROM [Entries]
JOIN [Documents] ON
[Documents].[DocID]=[Entries].[DocID]
WHERE
[Entries].[ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntriesByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getEntriesByContentID Error on Creation'
GO
/****** Object: StoredProcedure [getEntriesByDocID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getEntriesByDocID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getEntriesByDocID];
GO
CREATE PROCEDURE [dbo].[getEntriesByDocID]
(
@DocID int
)
AS
SELECT
[Entries].[ContentID],
[Entries].[DocID],
[Entries].[DTS],
[Entries].[UserID],
[Entries].[LastChanged],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID]
FROM [Entries]
JOIN [Contents] ON
[Contents].[ContentID]=[Entries].[ContentID]
WHERE
[Entries].[DocID]=@DocID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getEntriesByDocID Succeeded'
ELSE PRINT 'Procedure Creation: getEntriesByDocID Error on Creation'
GO
/****** Object: StoredProcedure [getFigures] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFigures]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFigures];
GO
CREATE PROCEDURE [dbo].[getFigures]
AS
SELECT
[FigureID],
[ROFstID],
[ImageID],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Figures]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFigures Succeeded'
ELSE PRINT 'Procedure Creation: getFigures Error on Creation'
GO
/****** Object: StoredProcedure [updateFigure] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateFigure]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateFigure];
GO
CREATE PROCEDURE [dbo].[updateFigure]
(
@FigureID int,
@ROFstID int,
@ImageID int,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Figures]
SET
[ROFstID]=@ROFstID,
[ImageID]=@ImageID,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [FigureID]=@FigureID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Figures] WHERE [FigureID]=@FigureID)
RAISERROR('Figure record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Figure has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Figures] WHERE [FigureID]=@FigureID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateFigure Succeeded'
ELSE PRINT 'Procedure Creation: updateFigure Error on Creation'
GO
/****** Object: StoredProcedure [addFigure] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addFigure]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addFigure];
GO
CREATE PROCEDURE [dbo].[addFigure]
(
@ROFstID int,
@ImageID int,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newFigureID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Figures]
(
[ROFstID],
[ImageID],
[Config],
[DTS],
[UserID]
)
VALUES
(
@ROFstID,
@ImageID,
@Config,
@DTS,
@UserID
)
SELECT @newFigureID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Figures] WHERE [FigureID]=@newFigureID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addFigure Succeeded'
ELSE PRINT 'Procedure Creation: addFigure Error on Creation'
GO
/****** Object: StoredProcedure [deleteFigure] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteFigure]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteFigure];
GO
CREATE PROCEDURE [dbo].[deleteFigure]
(
@FigureID int
)
AS
DELETE [Figures]
WHERE [FigureID] = @FigureID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteFigure Succeeded'
ELSE PRINT 'Procedure Creation: deleteFigure Error on Creation'
GO
/****** Object: StoredProcedure [getFigure] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFigure]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFigure];
GO
CREATE PROCEDURE [dbo].[getFigure]
(
@FigureID int
)
AS
SELECT
[FigureID],
[ROFstID],
[ImageID],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Figures]
WHERE [FigureID]=@FigureID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFigure Succeeded'
ELSE PRINT 'Procedure Creation: getFigure Error on Creation'
GO
/****** Object: StoredProcedure [getFigureByROFstID_ImageID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFigureByROFstID_ImageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFigureByROFstID_ImageID];
GO
CREATE PROCEDURE [dbo].[getFigureByROFstID_ImageID]
(
@ROFstID int,
@ImageID int
)
AS
SELECT
[FigureID],
[ROFstID],
[ImageID],
[Config],
[DTS],
[UserID],
[LastChanged]
FROM [Figures]
WHERE [ROFstID]=@ROFstID AND [ImageID]=@ImageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFigureByROFstID_ImageID Succeeded'
ELSE PRINT 'Procedure Creation: getFigureByROFstID_ImageID Error on Creation'
GO
/****** Object: StoredProcedure [existsFigure] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsFigure]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsFigure];
GO
CREATE PROCEDURE [dbo].[existsFigure]
(
@FigureID int
)
AS
SELECT COUNT(*)
FROM [Figures] WHERE [FigureID]=@FigureID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsFigure Succeeded'
ELSE PRINT 'Procedure Creation: existsFigure Error on Creation'
GO
/****** Object: StoredProcedure [getFiguresByROFstID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFiguresByROFstID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFiguresByROFstID];
GO
CREATE PROCEDURE [dbo].[getFiguresByROFstID]
(
@ROFstID int
)
AS
SELECT
[Figures].[FigureID],
[Figures].[ROFstID],
[Figures].[ImageID],
[Figures].[Config],
[Figures].[DTS],
[Figures].[UserID],
[Figures].[LastChanged],
[ROImages].[RODbID] [ROImage_RODbID],
[ROImages].[FileName] [ROImage_FileName],
[ROImages].[Content] [ROImage_Content],
[ROImages].[Config] [ROImage_Config],
[ROImages].[DTS] [ROImage_DTS],
[ROImages].[UserID] [ROImage_UserID]
FROM [Figures]
JOIN [ROImages] ON
[ROImages].[ImageID]=[Figures].[ImageID]
WHERE
[Figures].[ROFstID]=@ROFstID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFiguresByROFstID Succeeded'
ELSE PRINT 'Procedure Creation: getFiguresByROFstID Error on Creation'
GO
/****** Object: StoredProcedure [getFiguresByImageID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFiguresByImageID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFiguresByImageID];
GO
CREATE PROCEDURE [dbo].[getFiguresByImageID]
(
@ImageID int
)
AS
SELECT
[Figures].[FigureID],
[Figures].[ROFstID],
[Figures].[ImageID],
[Figures].[Config],
[Figures].[DTS],
[Figures].[UserID],
[Figures].[LastChanged],
[ROFsts].[RODbID] [ROFst_RODbID],
[ROFsts].[ROLookup] [ROFst_ROLookup],
[ROFsts].[Config] [ROFst_Config],
[ROFsts].[DTS] [ROFst_DTS],
[ROFsts].[UserID] [ROFst_UserID]
FROM [Figures]
JOIN [ROFsts] ON
[ROFsts].[ROFstID]=[Figures].[ROFstID]
WHERE
[Figures].[ImageID]=@ImageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFiguresByImageID Succeeded'
ELSE PRINT 'Procedure Creation: getFiguresByImageID Error on Creation'
GO
/****** Object: StoredProcedure [getFolders] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFolders]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFolders];
GO
CREATE PROCEDURE [dbo].[getFolders]
AS
SELECT
[FolderID],
[ParentID],
[DBID],
[Name],
[Title],
[ShortName],
[FormatID],
[ManualOrder],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
FROM [Folders]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFolders Succeeded'
ELSE PRINT 'Procedure Creation: getFolders Error on Creation'
GO
/****** Object: StoredProcedure [updateFolder] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateFolder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateFolder];
GO
CREATE PROCEDURE [dbo].[updateFolder]
(
@FolderID int,
@ParentID int,
@DBID int,
@Name nvarchar(100),
@Title nvarchar(510)=null,
@ShortName nvarchar(20),
@FormatID int=null,
@ManualOrder float=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Folders]
SET
[ParentID]=@ParentID,
[DBID]=@DBID,
[Name]=@Name,
[Title]=@Title,
[ShortName]=@ShortName,
[FormatID]=@FormatID,
[ManualOrder]=@ManualOrder,
[Config]=@Config,
[DTS]=@DTS,
[UsrID]=@UsrID
WHERE [FolderID]=@FolderID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Folders] WHERE [FolderID]=@FolderID)
RAISERROR('Folder record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Folder has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Folders] WHERE [FolderID]=@FolderID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateFolder Succeeded'
ELSE PRINT 'Procedure Creation: updateFolder Error on Creation'
GO
/****** Object: StoredProcedure [addFolder] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addFolder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addFolder];
GO
CREATE PROCEDURE [dbo].[addFolder]
(
@ParentID int,
@DBID int,
@Name nvarchar(100),
@Title nvarchar(510)=null,
@ShortName nvarchar(20),
@FormatID int=null,
@ManualOrder float=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@newFolderID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Folders]
(
[ParentID],
[DBID],
[Name],
[Title],
[ShortName],
[FormatID],
[ManualOrder],
[Config],
[DTS],
[UsrID]
)
VALUES
(
@ParentID,
@DBID,
@Name,
@Title,
@ShortName,
@FormatID,
@ManualOrder,
@Config,
@DTS,
@UsrID
)
SELECT @newFolderID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Folders] WHERE [FolderID]=@newFolderID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addFolder Succeeded'
ELSE PRINT 'Procedure Creation: addFolder Error on Creation'
GO
/****** Object: StoredProcedure [deleteFolder] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteFolder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteFolder];
GO
CREATE PROCEDURE [dbo].[deleteFolder]
(
@FolderID int
)
AS
DELETE [Assignments]
WHERE [FolderID]=@FolderID
DELETE [DocVersions]
WHERE [FolderID]=@FolderID
DELETE [Folders]
WHERE [FolderID] = @FolderID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteFolder Succeeded'
ELSE PRINT 'Procedure Creation: deleteFolder Error on Creation'
GO
/****** Object: StoredProcedure [getFolder] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFolder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFolder];
GO
CREATE PROCEDURE [dbo].[getFolder]
(
@FolderID int
)
AS
SELECT
[FolderID],
[ParentID],
[DBID],
[Name],
[Title],
[ShortName],
[FormatID],
[ManualOrder],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
FROM [Folders]
WHERE [FolderID]=@FolderID
SELECT
[Assignments].[AID],
[Assignments].[GID],
[Assignments].[RID],
[Assignments].[FolderID],
[Assignments].[StartDate],
[Assignments].[EndDate],
[Assignments].[DTS],
[Assignments].[UsrID],
[Assignments].[LastChanged],
[Groups].[GroupName] [Group_GroupName],
[Groups].[GroupType] [Group_GroupType],
[Groups].[Config] [Group_Config],
[Groups].[DTS] [Group_DTS],
[Groups].[UsrID] [Group_UsrID],
[Roles].[Name] [Role_Name],
[Roles].[Title] [Role_Title],
[Roles].[DTS] [Role_DTS],
[Roles].[UsrID] [Role_UsrID]
FROM [Assignments]
JOIN [Groups] ON
[Groups].[GID]=[Assignments].[GID]
JOIN [Roles] ON
[Roles].[RID]=[Assignments].[RID]
WHERE
[Assignments].[FolderID]=@FolderID
SELECT
[DocVersions].[VersionID],
[DocVersions].[FolderID],
[DocVersions].[VersionType],
[DocVersions].[Name],
[DocVersions].[Title],
[DocVersions].[ItemID],
[DocVersions].[FormatID],
[DocVersions].[Config],
[DocVersions].[DTS],
[DocVersions].[UserID],
[DocVersions].[LastChanged]
FROM [DocVersions]
WHERE
[DocVersions].[FolderID]=@FolderID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFolder Succeeded'
ELSE PRINT 'Procedure Creation: getFolder Error on Creation'
GO
/****** Object: StoredProcedure [getFolderByParentID_Name] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFolderByParentID_Name]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFolderByParentID_Name];
GO
CREATE PROCEDURE [dbo].[getFolderByParentID_Name]
(
@ParentID int,
@Name nvarchar(100)
)
AS
SELECT
[FolderID],
[ParentID],
[DBID],
[Name],
[Title],
[ShortName],
[FormatID],
[ManualOrder],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
FROM [Folders]
WHERE [ParentID]=@ParentID AND [Name]=@Name
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFolderByParentID_Name Succeeded'
ELSE PRINT 'Procedure Creation: getFolderByParentID_Name Error on Creation'
GO
/****** Object: StoredProcedure [getParentFolder] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParentFolder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getParentFolder];
GO
CREATE PROCEDURE [dbo].[getParentFolder]
(
@ParentID int
)
AS
SELECT
[FolderID],
[ParentID],
[DBID],
[Name],
[Title],
[ShortName],
[FormatID],
[ManualOrder],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
FROM [Folders]
WHERE
[FolderID]=@ParentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getParentFolder Succeeded'
ELSE PRINT 'Procedure Creation: getParentFolder Error on Creation'
GO
/****** Object: StoredProcedure [existsFolder] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsFolder]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsFolder];
GO
CREATE PROCEDURE [dbo].[existsFolder]
(
@FolderID int
)
AS
SELECT COUNT(*)
FROM [Folders] WHERE [FolderID]=@FolderID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsFolder Succeeded'
ELSE PRINT 'Procedure Creation: existsFolder Error on Creation'
GO
/****** Object: StoredProcedure [getChildFolders] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getChildFolders]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getChildFolders];
GO
CREATE PROCEDURE [dbo].[getChildFolders]
(
@ParentID int
)
AS
SELECT
[FolderID],
[ParentID],
[DBID],
[Name],
[Title],
[ShortName],
[FormatID],
[ManualOrder],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
FROM [Folders] WHERE [ParentID]=@ParentID AND [FolderID]<>@ParentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChildFolders Succeeded'
ELSE PRINT 'Procedure Creation: getChildFolders Error on Creation'
GO
/****** Object: StoredProcedure [getFoldersByDBID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFoldersByDBID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFoldersByDBID];
GO
CREATE PROCEDURE [dbo].[getFoldersByDBID]
(
@DBID int
)
AS
SELECT
[Folders].[FolderID],
[Folders].[ParentID],
[Folders].[DBID],
[Folders].[Name],
[Folders].[Title],
[Folders].[ShortName],
[Folders].[FormatID],
[Folders].[ManualOrder],
[Folders].[Config],
[Folders].[DTS],
[Folders].[UsrID],
[Folders].[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
FROM [Folders]
WHERE
[Folders].[DBID]=@DBID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFoldersByDBID Succeeded'
ELSE PRINT 'Procedure Creation: getFoldersByDBID Error on Creation'
GO
/****** Object: StoredProcedure [getFoldersByFormatID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFoldersByFormatID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFoldersByFormatID];
GO
CREATE PROCEDURE [dbo].[getFoldersByFormatID]
(
@FormatID int
)
AS
SELECT
[Folders].[FolderID],
[Folders].[ParentID],
[Folders].[DBID],
[Folders].[Name],
[Folders].[Title],
[Folders].[ShortName],
[Folders].[FormatID],
[Folders].[ManualOrder],
[Folders].[Config],
[Folders].[DTS],
[Folders].[UsrID],
[Folders].[LastChanged],
[Connections].[Name] [Connection_Name],
[Connections].[Title] [Connection_Title],
[Connections].[ConnectionString] [Connection_ConnectionString],
[Connections].[ServerType] [Connection_ServerType],
[Connections].[Config] [Connection_Config],
[Connections].[DTS] [Connection_DTS],
[Connections].[UsrID] [Connection_UsrID],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[FolderID]=[Folders].[FolderID]) [AssignmentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FolderID]=[Folders].[FolderID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] [Children] WHERE [Children].[ParentID]=[Folders].[FolderID]) [ChildCount]
FROM [Folders]
JOIN [Connections] ON
[Connections].[DBID]=[Folders].[DBID]
WHERE
[Folders].[FormatID]=@FormatID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFoldersByFormatID Succeeded'
ELSE PRINT 'Procedure Creation: getFoldersByFormatID Error on Creation'
GO
/****** Object: StoredProcedure [getFormats] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFormats]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFormats];
GO
CREATE PROCEDURE [dbo].[getFormats]
AS
SELECT
[FormatID],
[ParentID],
[Name],
[Description],
[Data],
[GenMac],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
FROM [Formats]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFormats Succeeded'
ELSE PRINT 'Procedure Creation: getFormats Error on Creation'
GO
/****** Object: StoredProcedure [updateFormat] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateFormat]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateFormat];
GO
CREATE PROCEDURE [dbo].[updateFormat]
(
@FormatID int,
@ParentID int,
@Name nvarchar(20),
@Description nvarchar(250)=null,
@Data xml,
@GenMac xml=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Formats]
SET
[ParentID]=@ParentID,
[Name]=@Name,
[Description]=@Description,
[Data]=@Data,
[GenMac]=@GenMac,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [FormatID]=@FormatID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Formats] WHERE [FormatID]=@FormatID)
RAISERROR('Format record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Format has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Formats] WHERE [FormatID]=@FormatID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateFormat Succeeded'
ELSE PRINT 'Procedure Creation: updateFormat Error on Creation'
GO
/****** Object: StoredProcedure [addFormat] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addFormat]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addFormat];
GO
CREATE PROCEDURE [dbo].[addFormat]
(
@ParentID int,
@Name nvarchar(20),
@Description nvarchar(250)=null,
@Data xml,
@GenMac xml=null,
@DTS datetime,
@UserID nvarchar(100),
@newFormatID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Formats]
(
[ParentID],
[Name],
[Description],
[Data],
[GenMac],
[DTS],
[UserID]
)
VALUES
(
@ParentID,
@Name,
@Description,
@Data,
@GenMac,
@DTS,
@UserID
)
SELECT @newFormatID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Formats] WHERE [FormatID]=@newFormatID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addFormat Succeeded'
ELSE PRINT 'Procedure Creation: addFormat Error on Creation'
GO
/****** Object: StoredProcedure [deleteFormat] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteFormat]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteFormat];
GO
CREATE PROCEDURE [dbo].[deleteFormat]
(
@FormatID int
)
AS
DELETE [Contents]
WHERE [FormatID]=@FormatID
DELETE [DocVersions]
WHERE [FormatID]=@FormatID
DELETE [Folders]
WHERE [FormatID]=@FormatID
DELETE [Formats]
WHERE [FormatID] = @FormatID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteFormat Succeeded'
ELSE PRINT 'Procedure Creation: deleteFormat Error on Creation'
GO
/****** Object: StoredProcedure [getFormat] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFormat]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFormat];
GO
CREATE PROCEDURE [dbo].[getFormat]
(
@FormatID int
)
AS
SELECT
[FormatID],
[ParentID],
[Name],
[Description],
[Data],
[GenMac],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
FROM [Formats]
WHERE [FormatID]=@FormatID
SELECT
[Contents].[ContentID],
[Contents].[Number],
[Contents].[Text],
[Contents].[Type],
[Contents].[FormatID],
[Contents].[Config],
[Contents].[DTS],
[Contents].[UserID],
[Contents].[LastChanged]
FROM [Contents]
WHERE
[Contents].[FormatID]=@FormatID
SELECT
[DocVersions].[VersionID],
[DocVersions].[FolderID],
[DocVersions].[VersionType],
[DocVersions].[Name],
[DocVersions].[Title],
[DocVersions].[ItemID],
[DocVersions].[FormatID],
[DocVersions].[Config],
[DocVersions].[DTS],
[DocVersions].[UserID],
[DocVersions].[LastChanged],
[Folders].[ParentID] [Folder_ParentID],
[Folders].[DBID] [Folder_DBID],
[Folders].[Name] [Folder_Name],
[Folders].[Title] [Folder_Title],
[Folders].[ShortName] [Folder_ShortName],
[Folders].[FormatID] [Folder_FormatID],
[Folders].[ManualOrder] [Folder_ManualOrder],
[Folders].[Config] [Folder_Config],
[Folders].[DTS] [Folder_DTS],
[Folders].[UsrID] [Folder_UsrID]
FROM [DocVersions]
JOIN [Folders] ON
[Folders].[FolderID]=[DocVersions].[FolderID]
WHERE
[DocVersions].[FormatID]=@FormatID
SELECT
[Folders].[FolderID],
[Folders].[ParentID],
[Folders].[DBID],
[Folders].[Name],
[Folders].[Title],
[Folders].[ShortName],
[Folders].[FormatID],
[Folders].[ManualOrder],
[Folders].[Config],
[Folders].[DTS],
[Folders].[UsrID],
[Folders].[LastChanged],
[Connections].[Name] [Connection_Name],
[Connections].[Title] [Connection_Title],
[Connections].[ConnectionString] [Connection_ConnectionString],
[Connections].[ServerType] [Connection_ServerType],
[Connections].[Config] [Connection_Config],
[Connections].[DTS] [Connection_DTS],
[Connections].[UsrID] [Connection_UsrID]
FROM [Folders]
JOIN [Connections] ON
[Connections].[DBID]=[Folders].[DBID]
WHERE
[Folders].[FormatID]=@FormatID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFormat Succeeded'
ELSE PRINT 'Procedure Creation: getFormat Error on Creation'
GO
/****** Object: StoredProcedure [getFormatByParentID_Name] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getFormatByParentID_Name]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getFormatByParentID_Name];
GO
CREATE PROCEDURE [dbo].[getFormatByParentID_Name]
(
@ParentID int,
@Name nvarchar(20)
)
AS
SELECT
[FormatID],
[ParentID],
[Name],
[Description],
[Data],
[GenMac],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
FROM [Formats]
WHERE [ParentID]=@ParentID AND [Name]=@Name
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getFormatByParentID_Name Succeeded'
ELSE PRINT 'Procedure Creation: getFormatByParentID_Name Error on Creation'
GO
/****** Object: StoredProcedure [getParentFormat] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParentFormat]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getParentFormat];
GO
CREATE PROCEDURE [dbo].[getParentFormat]
(
@ParentID int
)
AS
SELECT
[FormatID],
[ParentID],
[Name],
[Description],
[Data],
[GenMac],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
FROM [Formats]
WHERE
[FormatID]=@ParentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getParentFormat Succeeded'
ELSE PRINT 'Procedure Creation: getParentFormat Error on Creation'
GO
/****** Object: StoredProcedure [existsFormat] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsFormat]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsFormat];
GO
CREATE PROCEDURE [dbo].[existsFormat]
(
@FormatID int
)
AS
SELECT COUNT(*)
FROM [Formats] WHERE [FormatID]=@FormatID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsFormat Succeeded'
ELSE PRINT 'Procedure Creation: existsFormat Error on Creation'
GO
/****** Object: StoredProcedure [getChildFormats] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getChildFormats]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getChildFormats];
GO
CREATE PROCEDURE [dbo].[getChildFormats]
(
@ParentID int
)
AS
SELECT
[FormatID],
[ParentID],
[Name],
[Description],
[Data],
[GenMac],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Contents] WHERE [Contents].[FormatID]=[Formats].[FormatID]) [ContentCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[FormatID]=[Formats].[FormatID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Folders] WHERE [Folders].[FormatID]=[Formats].[FormatID]) [FolderCount],
(SELECT COUNT(*) FROM [Formats] [Children] WHERE [Children].[ParentID]=[Formats].[FormatID]) [ChildCount]
FROM [Formats] WHERE [ParentID]=@ParentID AND [FormatID]<>@ParentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChildFormats Succeeded'
ELSE PRINT 'Procedure Creation: getChildFormats Error on Creation'
GO
/****** Object: StoredProcedure [getGroups] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGroups]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getGroups];
GO
CREATE PROCEDURE [dbo].[getGroups]
AS
SELECT
[GID],
[GroupName],
[GroupType],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[GID]=[Groups].[GID]) [AssignmentCount],
(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[GID]=[Groups].[GID]) [MembershipCount]
FROM [Groups]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGroups Succeeded'
ELSE PRINT 'Procedure Creation: getGroups Error on Creation'
GO
/****** Object: StoredProcedure [updateGroup] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateGroup]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateGroup];
GO
CREATE PROCEDURE [dbo].[updateGroup]
(
@GID int,
@GroupName nvarchar(50),
@GroupType int=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Groups]
SET
[GroupName]=@GroupName,
[GroupType]=@GroupType,
[Config]=@Config,
[DTS]=@DTS,
[UsrID]=@UsrID
WHERE [GID]=@GID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Groups] WHERE [GID]=@GID)
RAISERROR('Group record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Group has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Groups] WHERE [GID]=@GID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateGroup Succeeded'
ELSE PRINT 'Procedure Creation: updateGroup Error on Creation'
GO
/****** Object: StoredProcedure [addGroup] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addGroup]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addGroup];
GO
CREATE PROCEDURE [dbo].[addGroup]
(
@GroupName nvarchar(50),
@GroupType int=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@newGID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Groups]
(
[GroupName],
[GroupType],
[Config],
[DTS],
[UsrID]
)
VALUES
(
@GroupName,
@GroupType,
@Config,
@DTS,
@UsrID
)
SELECT @newGID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Groups] WHERE [GID]=@newGID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addGroup Succeeded'
ELSE PRINT 'Procedure Creation: addGroup Error on Creation'
GO
/****** Object: StoredProcedure [deleteGroup] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteGroup]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteGroup];
GO
CREATE PROCEDURE [dbo].[deleteGroup]
(
@GID int
)
AS
DELETE [Assignments]
WHERE [GID]=@GID
DELETE [Memberships]
WHERE [GID]=@GID
DELETE [Groups]
WHERE [GID] = @GID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteGroup Succeeded'
ELSE PRINT 'Procedure Creation: deleteGroup Error on Creation'
GO
/****** Object: StoredProcedure [getGroup] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGroup]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getGroup];
GO
CREATE PROCEDURE [dbo].[getGroup]
(
@GID int
)
AS
SELECT
[GID],
[GroupName],
[GroupType],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[GID]=[Groups].[GID]) [AssignmentCount],
(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[GID]=[Groups].[GID]) [MembershipCount]
FROM [Groups]
WHERE [GID]=@GID
SELECT
[Assignments].[AID],
[Assignments].[GID],
[Assignments].[RID],
[Assignments].[FolderID],
[Assignments].[StartDate],
[Assignments].[EndDate],
[Assignments].[DTS],
[Assignments].[UsrID],
[Assignments].[LastChanged],
[Folders].[ParentID] [Folder_ParentID],
[Folders].[DBID] [Folder_DBID],
[Folders].[Name] [Folder_Name],
[Folders].[Title] [Folder_Title],
[Folders].[ShortName] [Folder_ShortName],
[Folders].[FormatID] [Folder_FormatID],
[Folders].[ManualOrder] [Folder_ManualOrder],
[Folders].[Config] [Folder_Config],
[Folders].[DTS] [Folder_DTS],
[Folders].[UsrID] [Folder_UsrID],
[Roles].[Name] [Role_Name],
[Roles].[Title] [Role_Title],
[Roles].[DTS] [Role_DTS],
[Roles].[UsrID] [Role_UsrID]
FROM [Assignments]
JOIN [Folders] ON
[Folders].[FolderID]=[Assignments].[FolderID]
JOIN [Roles] ON
[Roles].[RID]=[Assignments].[RID]
WHERE
[Assignments].[GID]=@GID
SELECT
[Memberships].[UGID],
[Memberships].[UID],
[Memberships].[GID],
[Memberships].[StartDate],
[Memberships].[EndDate],
[Memberships].[Config],
[Memberships].[DTS],
[Memberships].[UsrID],
[Memberships].[LastChanged],
[Users].[UserID] [User_UserID],
[Users].[FirstName] [User_FirstName],
[Users].[MiddleName] [User_MiddleName],
[Users].[LastName] [User_LastName],
[Users].[Suffix] [User_Suffix],
[Users].[CourtesyTitle] [User_CourtesyTitle],
[Users].[PhoneNumber] [User_PhoneNumber],
[Users].[CFGName] [User_CFGName],
[Users].[UserLogin] [User_UserLogin],
[Users].[UserName] [User_UserName],
[Users].[Config] [User_Config],
[Users].[DTS] [User_DTS],
[Users].[UsrID] [User_UsrID]
FROM [Memberships]
JOIN [Users] ON
[Users].[UID]=[Memberships].[UID]
WHERE
[Memberships].[GID]=@GID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGroup Succeeded'
ELSE PRINT 'Procedure Creation: getGroup Error on Creation'
GO
/****** Object: StoredProcedure [getGroupByGroupName] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGroupByGroupName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getGroupByGroupName];
GO
CREATE PROCEDURE [dbo].[getGroupByGroupName]
(
@GroupName nvarchar(50)
)
AS
SELECT
[GID],
[GroupName],
[GroupType],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[GID]=[Groups].[GID]) [AssignmentCount],
(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[GID]=[Groups].[GID]) [MembershipCount]
FROM [Groups]
WHERE [GroupName]=@GroupName
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGroupByGroupName Succeeded'
ELSE PRINT 'Procedure Creation: getGroupByGroupName Error on Creation'
GO
/****** Object: StoredProcedure [existsGroup] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsGroup]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsGroup];
GO
CREATE PROCEDURE [dbo].[existsGroup]
(
@GID int
)
AS
SELECT COUNT(*)
FROM [Groups] WHERE [GID]=@GID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsGroup Succeeded'
ELSE PRINT 'Procedure Creation: existsGroup Error on Creation'
GO
/****** Object: StoredProcedure [getItems] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getItems]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getItems];
GO
CREATE PROCEDURE [dbo].[getItems]
AS
SELECT
[ItemID],
[PreviousID],
[ContentID],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
FROM [Items]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getItems Succeeded'
ELSE PRINT 'Procedure Creation: getItems Error on Creation'
GO
/****** Object: StoredProcedure [updateItem] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateItem]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateItem];
GO
CREATE PROCEDURE [dbo].[updateItem]
(
@ItemID int,
@PreviousID int=null,
@ContentID int,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Items]
SET
[PreviousID]=@PreviousID,
[ContentID]=@ContentID,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [ItemID]=@ItemID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Items] WHERE [ItemID]=@ItemID)
RAISERROR('Item record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Item has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Items] WHERE [ItemID]=@ItemID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateItem Succeeded'
ELSE PRINT 'Procedure Creation: updateItem Error on Creation'
GO
/****** Object: StoredProcedure [addItem] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addItem]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addItem];
GO
CREATE PROCEDURE [dbo].[addItem]
(
@PreviousID int=null,
@ContentID int,
@DTS datetime,
@UserID nvarchar(100),
@newItemID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Items]
(
[PreviousID],
[ContentID],
[DTS],
[UserID]
)
VALUES
(
@PreviousID,
@ContentID,
@DTS,
@UserID
)
SELECT @newItemID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Items] WHERE [ItemID]=@newItemID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addItem Succeeded'
ELSE PRINT 'Procedure Creation: addItem Error on Creation'
GO
/****** Object: StoredProcedure [deleteItem] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteItem]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteItem];
GO
CREATE PROCEDURE [dbo].[deleteItem]
(
@ItemID int
)
AS
DELETE [Annotations]
WHERE [ItemID]=@ItemID
DELETE [DocVersions]
WHERE [ItemID]=@ItemID
DELETE [Parts]
WHERE [ItemID]=@ItemID
DELETE [Transitions]
WHERE [RangeID]=@ItemID
DELETE [Transitions]
WHERE [ToID]=@ItemID
DELETE [Items]
WHERE [ItemID] = @ItemID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteItem Succeeded'
ELSE PRINT 'Procedure Creation: deleteItem Error on Creation'
GO
/****** Object: StoredProcedure [getItem] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getItem]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getItem];
GO
CREATE PROCEDURE [dbo].[getItem]
(
@ItemID int
)
AS
SELECT
[ItemID],
[PreviousID],
[ContentID],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
FROM [Items]
WHERE [ItemID]=@ItemID
SELECT
[Annotations].[AnnotationID],
[Annotations].[ItemID],
[Annotations].[TypeID],
[Annotations].[RtfText],
[Annotations].[SearchText],
[Annotations].[Config],
[Annotations].[DTS],
[Annotations].[UserID],
[Annotations].[LastChanged],
[AnnotationTypes].[Name] [AnnotationType_Name],
[AnnotationTypes].[Config] [AnnotationType_Config],
[AnnotationTypes].[DTS] [AnnotationType_DTS],
[AnnotationTypes].[UserID] [AnnotationType_UserID]
FROM [Annotations]
JOIN [AnnotationTypes] ON
[AnnotationTypes].[TypeID]=[Annotations].[TypeID]
WHERE
[Annotations].[ItemID]=@ItemID
SELECT
[DocVersions].[VersionID],
[DocVersions].[FolderID],
[DocVersions].[VersionType],
[DocVersions].[Name],
[DocVersions].[Title],
[DocVersions].[ItemID],
[DocVersions].[FormatID],
[DocVersions].[Config],
[DocVersions].[DTS],
[DocVersions].[UserID],
[DocVersions].[LastChanged],
[Folders].[ParentID] [Folder_ParentID],
[Folders].[DBID] [Folder_DBID],
[Folders].[Name] [Folder_Name],
[Folders].[Title] [Folder_Title],
[Folders].[ShortName] [Folder_ShortName],
[Folders].[FormatID] [Folder_FormatID],
[Folders].[ManualOrder] [Folder_ManualOrder],
[Folders].[Config] [Folder_Config],
[Folders].[DTS] [Folder_DTS],
[Folders].[UsrID] [Folder_UsrID]
FROM [DocVersions]
JOIN [Folders] ON
[Folders].[FolderID]=[DocVersions].[FolderID]
WHERE
[DocVersions].[ItemID]=@ItemID
SELECT
[Parts].[ContentID],
[Parts].[FromType],
[Parts].[ItemID],
[Parts].[DTS],
[Parts].[UserID],
[Parts].[LastChanged],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID]
FROM [Parts]
JOIN [Contents] ON
[Contents].[ContentID]=[Parts].[ContentID]
WHERE
[Parts].[ItemID]=@ItemID
SELECT
[Transitions].[TransitionID],
[Transitions].[FromID],
[Transitions].[ToID],
[Transitions].[RangeID],
[Transitions].[TranType],
[Transitions].[Config],
[Transitions].[DTS],
[Transitions].[UserID],
[Transitions].[LastChanged],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID]
FROM [Transitions]
JOIN [Contents] ON
[Contents].[ContentID]=[Transitions].[FromID]
WHERE
[Transitions].[RangeID]=@ItemID
SELECT
[Transitions].[TransitionID],
[Transitions].[FromID],
[Transitions].[ToID],
[Transitions].[RangeID],
[Transitions].[TranType],
[Transitions].[Config],
[Transitions].[DTS],
[Transitions].[UserID],
[Transitions].[LastChanged],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID]
FROM [Transitions]
JOIN [Contents] ON
[Contents].[ContentID]=[Transitions].[FromID]
WHERE
[Transitions].[ToID]=@ItemID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getItem Succeeded'
ELSE PRINT 'Procedure Creation: getItem Error on Creation'
GO
/****** Object: StoredProcedure [getPreviousItem] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPreviousItem]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getPreviousItem];
GO
CREATE PROCEDURE [dbo].[getPreviousItem]
(
@PreviousID int
)
AS
SELECT
[ItemID],
[PreviousID],
[ContentID],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
FROM [Items]
WHERE
[ItemID]=@PreviousID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPreviousItem Succeeded'
ELSE PRINT 'Procedure Creation: getPreviousItem Error on Creation'
GO
/****** Object: StoredProcedure [existsItem] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsItem]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsItem];
GO
CREATE PROCEDURE [dbo].[existsItem]
(
@ItemID int
)
AS
SELECT COUNT(*)
FROM [Items] WHERE [ItemID]=@ItemID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsItem Succeeded'
ELSE PRINT 'Procedure Creation: existsItem Error on Creation'
GO
/****** Object: StoredProcedure [getNextItems] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getNextItems]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getNextItems];
GO
CREATE PROCEDURE [dbo].[getNextItems]
(
@PreviousID int
)
AS
SELECT
[ItemID],
[PreviousID],
[ContentID],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
FROM [Items] WHERE [PreviousID]=@PreviousID AND [ItemID]<>@PreviousID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getNextItems Succeeded'
ELSE PRINT 'Procedure Creation: getNextItems Error on Creation'
GO
/****** Object: StoredProcedure [getItemsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getItemsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getItemsByContentID];
GO
CREATE PROCEDURE [dbo].[getItemsByContentID]
(
@ContentID int
)
AS
SELECT
[Items].[ItemID],
[Items].[PreviousID],
[Items].[ContentID],
[Items].[DTS],
[Items].[UserID],
[Items].[LastChanged],
(SELECT COUNT(*) FROM [Annotations] WHERE [Annotations].[ItemID]=[Items].[ItemID]) [AnnotationCount],
(SELECT COUNT(*) FROM [DocVersions] WHERE [DocVersions].[ItemID]=[Items].[ItemID]) [DocVersionCount],
(SELECT COUNT(*) FROM [Items] [Next] WHERE [Next].[PreviousID]=[Items].[ItemID]) [NextCount],
(SELECT COUNT(*) FROM [Parts] WHERE [Parts].[ItemID]=[Items].[ItemID]) [PartCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[RangeID]=[Items].[ItemID]) [Transition_RangeIDCount],
(SELECT COUNT(*) FROM [Transitions] WHERE [Transitions].[ToID]=[Items].[ItemID]) [Transition_ToIDCount]
FROM [Items]
WHERE
[Items].[ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getItemsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getItemsByContentID Error on Creation'
GO
/****** Object: StoredProcedure [getMemberships] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getMemberships]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getMemberships];
GO
CREATE PROCEDURE [dbo].[getMemberships]
AS
SELECT
[UGID],
[UID],
[GID],
[StartDate],
[EndDate],
[Config],
[DTS],
[UsrID],
[LastChanged]
FROM [Memberships]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getMemberships Succeeded'
ELSE PRINT 'Procedure Creation: getMemberships Error on Creation'
GO
/****** Object: StoredProcedure [updateMembership] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateMembership]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateMembership];
GO
CREATE PROCEDURE [dbo].[updateMembership]
(
@UGID int,
@UID int,
@GID int,
@StartDate datetime,
@EndDate datetime=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Memberships]
SET
[UID]=@UID,
[GID]=@GID,
[StartDate]=@StartDate,
[EndDate]=@EndDate,
[Config]=@Config,
[DTS]=@DTS,
[UsrID]=@UsrID
WHERE [UGID]=@UGID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Memberships] WHERE [UGID]=@UGID)
RAISERROR('Membership record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Membership has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Memberships] WHERE [UGID]=@UGID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateMembership Succeeded'
ELSE PRINT 'Procedure Creation: updateMembership Error on Creation'
GO
/****** Object: StoredProcedure [addMembership] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addMembership]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addMembership];
GO
CREATE PROCEDURE [dbo].[addMembership]
(
@UID int,
@GID int,
@StartDate datetime,
@EndDate datetime=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@newUGID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Memberships]
(
[UID],
[GID],
[StartDate],
[EndDate],
[Config],
[DTS],
[UsrID]
)
VALUES
(
@UID,
@GID,
@StartDate,
@EndDate,
@Config,
@DTS,
@UsrID
)
SELECT @newUGID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Memberships] WHERE [UGID]=@newUGID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addMembership Succeeded'
ELSE PRINT 'Procedure Creation: addMembership Error on Creation'
GO
/****** Object: StoredProcedure [deleteMembership] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteMembership]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteMembership];
GO
CREATE PROCEDURE [dbo].[deleteMembership]
(
@UGID int
)
AS
DELETE [Memberships]
WHERE [UGID] = @UGID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteMembership Succeeded'
ELSE PRINT 'Procedure Creation: deleteMembership Error on Creation'
GO
/****** Object: StoredProcedure [getMembership] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getMembership]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getMembership];
GO
CREATE PROCEDURE [dbo].[getMembership]
(
@UGID int
)
AS
SELECT
[UGID],
[UID],
[GID],
[StartDate],
[EndDate],
[Config],
[DTS],
[UsrID],
[LastChanged]
FROM [Memberships]
WHERE [UGID]=@UGID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getMembership Succeeded'
ELSE PRINT 'Procedure Creation: getMembership Error on Creation'
GO
/****** Object: StoredProcedure [existsMembership] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsMembership]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsMembership];
GO
CREATE PROCEDURE [dbo].[existsMembership]
(
@UGID int
)
AS
SELECT COUNT(*)
FROM [Memberships] WHERE [UGID]=@UGID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsMembership Succeeded'
ELSE PRINT 'Procedure Creation: existsMembership Error on Creation'
GO
/****** Object: StoredProcedure [getMembershipsByGID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getMembershipsByGID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getMembershipsByGID];
GO
CREATE PROCEDURE [dbo].[getMembershipsByGID]
(
@GID int
)
AS
SELECT
[Memberships].[UGID],
[Memberships].[UID],
[Memberships].[GID],
[Memberships].[StartDate],
[Memberships].[EndDate],
[Memberships].[Config],
[Memberships].[DTS],
[Memberships].[UsrID],
[Memberships].[LastChanged],
[Users].[UserID] [User_UserID],
[Users].[FirstName] [User_FirstName],
[Users].[MiddleName] [User_MiddleName],
[Users].[LastName] [User_LastName],
[Users].[Suffix] [User_Suffix],
[Users].[CourtesyTitle] [User_CourtesyTitle],
[Users].[PhoneNumber] [User_PhoneNumber],
[Users].[CFGName] [User_CFGName],
[Users].[UserLogin] [User_UserLogin],
[Users].[UserName] [User_UserName],
[Users].[Config] [User_Config],
[Users].[DTS] [User_DTS],
[Users].[UsrID] [User_UsrID]
FROM [Memberships]
JOIN [Users] ON
[Users].[UID]=[Memberships].[UID]
WHERE
[Memberships].[GID]=@GID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getMembershipsByGID Succeeded'
ELSE PRINT 'Procedure Creation: getMembershipsByGID Error on Creation'
GO
/****** Object: StoredProcedure [getMembershipsByUID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getMembershipsByUID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getMembershipsByUID];
GO
CREATE PROCEDURE [dbo].[getMembershipsByUID]
(
@UID int
)
AS
SELECT
[Memberships].[UGID],
[Memberships].[UID],
[Memberships].[GID],
[Memberships].[StartDate],
[Memberships].[EndDate],
[Memberships].[Config],
[Memberships].[DTS],
[Memberships].[UsrID],
[Memberships].[LastChanged],
[Groups].[GroupName] [Group_GroupName],
[Groups].[GroupType] [Group_GroupType],
[Groups].[Config] [Group_Config],
[Groups].[DTS] [Group_DTS],
[Groups].[UsrID] [Group_UsrID]
FROM [Memberships]
JOIN [Groups] ON
[Groups].[GID]=[Memberships].[GID]
WHERE
[Memberships].[UID]=@UID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getMembershipsByUID Succeeded'
ELSE PRINT 'Procedure Creation: getMembershipsByUID Error on Creation'
GO
/****** Object: StoredProcedure [getParts] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParts]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getParts];
GO
CREATE PROCEDURE [dbo].[getParts]
AS
SELECT
[ContentID],
[FromType],
[ItemID],
[DTS],
[UserID],
[LastChanged]
FROM [Parts]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getParts Succeeded'
ELSE PRINT 'Procedure Creation: getParts Error on Creation'
GO
/****** Object: StoredProcedure [updatePart] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updatePart]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updatePart];
GO
CREATE PROCEDURE [dbo].[updatePart]
(
@ContentID int,
@FromType int,
@ItemID int,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Parts]
SET
[ItemID]=@ItemID,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [ContentID]=@ContentID AND [FromType]=@FromType AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Parts] WHERE [ContentID]=@ContentID AND [FromType]=@FromType)
RAISERROR('Part record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Part has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Parts] WHERE [ContentID]=@ContentID AND [FromType]=@FromType
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updatePart Succeeded'
ELSE PRINT 'Procedure Creation: updatePart Error on Creation'
GO
/****** Object: StoredProcedure [addPart] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addPart]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addPart];
GO
CREATE PROCEDURE [dbo].[addPart]
(
@ContentID int,
@FromType int,
@ItemID int,
@DTS datetime,
@UserID nvarchar(100),
@newLastChanged timestamp output
)
AS
INSERT INTO [Parts]
(
[ContentID],
[FromType],
[ItemID],
[DTS],
[UserID]
)
VALUES
(
@ContentID,
@FromType,
@ItemID,
@DTS,
@UserID
)
SELECT @newLastChanged=[LastChanged]
FROM [Parts] WHERE [ContentID]=@ContentID AND [FromType]=@FromType
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addPart Succeeded'
ELSE PRINT 'Procedure Creation: addPart Error on Creation'
GO
/****** Object: StoredProcedure [deletePart] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deletePart]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deletePart];
GO
CREATE PROCEDURE [dbo].[deletePart]
(
@ContentID int,
@FromType int
)
AS
DELETE [Parts]
WHERE [ContentID] = @ContentID AND [FromType] = @FromType
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deletePart Succeeded'
ELSE PRINT 'Procedure Creation: deletePart Error on Creation'
GO
/****** Object: StoredProcedure [getPart] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPart]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getPart];
GO
CREATE PROCEDURE [dbo].[getPart]
(
@ContentID int,
@FromType int
)
AS
SELECT
[ContentID],
[FromType],
[ItemID],
[DTS],
[UserID],
[LastChanged]
FROM [Parts]
WHERE [ContentID]=@ContentID AND [FromType]=@FromType
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPart Succeeded'
ELSE PRINT 'Procedure Creation: getPart Error on Creation'
GO
/****** Object: StoredProcedure [existsPart] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsPart]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsPart];
GO
CREATE PROCEDURE [dbo].[existsPart]
(
@ContentID int,
@FromType int
)
AS
SELECT COUNT(*)
FROM [Parts] WHERE [ContentID]=@ContentID AND [FromType]=@FromType
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsPart Succeeded'
ELSE PRINT 'Procedure Creation: existsPart Error on Creation'
GO
/****** Object: StoredProcedure [getPartsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPartsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getPartsByContentID];
GO
CREATE PROCEDURE [dbo].[getPartsByContentID]
(
@ContentID int
)
AS
SELECT
[Parts].[ContentID],
[Parts].[FromType],
[Parts].[ItemID],
[Parts].[DTS],
[Parts].[UserID],
[Parts].[LastChanged],
[Items].[PreviousID] [Item_PreviousID],
[Items].[ContentID] [Item_ContentID],
[Items].[DTS] [Item_DTS],
[Items].[UserID] [Item_UserID]
FROM [Parts]
JOIN [Items] ON
[Items].[ItemID]=[Parts].[ItemID]
WHERE
[Parts].[ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPartsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getPartsByContentID Error on Creation'
GO
/****** Object: StoredProcedure [getPartsByItemID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPartsByItemID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getPartsByItemID];
GO
CREATE PROCEDURE [dbo].[getPartsByItemID]
(
@ItemID int
)
AS
SELECT
[Parts].[ContentID],
[Parts].[FromType],
[Parts].[ItemID],
[Parts].[DTS],
[Parts].[UserID],
[Parts].[LastChanged],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID]
FROM [Parts]
JOIN [Contents] ON
[Contents].[ContentID]=[Parts].[ContentID]
WHERE
[Parts].[ItemID]=@ItemID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPartsByItemID Succeeded'
ELSE PRINT 'Procedure Creation: getPartsByItemID Error on Creation'
GO
/****** Object: StoredProcedure [getPermissions] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPermissions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getPermissions];
GO
CREATE PROCEDURE [dbo].[getPermissions]
AS
SELECT
[PID],
[RID],
[PermLevel],
[VersionType],
[PermValue],
[PermAD],
[StartDate],
[EndDate],
[Config],
[DTS],
[UsrID],
[LastChanged]
FROM [Permissions]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPermissions Succeeded'
ELSE PRINT 'Procedure Creation: getPermissions Error on Creation'
GO
/****** Object: StoredProcedure [updatePermission] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updatePermission]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updatePermission];
GO
CREATE PROCEDURE [dbo].[updatePermission]
(
@PID int,
@RID int,
@PermLevel int,
@VersionType int,
@PermValue int,
@PermAD int,
@StartDate datetime,
@EndDate datetime=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Permissions]
SET
[RID]=@RID,
[PermLevel]=@PermLevel,
[VersionType]=@VersionType,
[PermValue]=@PermValue,
[PermAD]=@PermAD,
[StartDate]=@StartDate,
[EndDate]=@EndDate,
[Config]=@Config,
[DTS]=@DTS,
[UsrID]=@UsrID
WHERE [PID]=@PID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Permissions] WHERE [PID]=@PID)
RAISERROR('Permission record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Permission has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Permissions] WHERE [PID]=@PID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updatePermission Succeeded'
ELSE PRINT 'Procedure Creation: updatePermission Error on Creation'
GO
/****** Object: StoredProcedure [addPermission] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addPermission]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addPermission];
GO
CREATE PROCEDURE [dbo].[addPermission]
(
@RID int,
@PermLevel int,
@VersionType int,
@PermValue int,
@PermAD int,
@StartDate datetime,
@EndDate datetime=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@newPID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Permissions]
(
[RID],
[PermLevel],
[VersionType],
[PermValue],
[PermAD],
[StartDate],
[EndDate],
[Config],
[DTS],
[UsrID]
)
VALUES
(
@RID,
@PermLevel,
@VersionType,
@PermValue,
@PermAD,
@StartDate,
@EndDate,
@Config,
@DTS,
@UsrID
)
SELECT @newPID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Permissions] WHERE [PID]=@newPID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addPermission Succeeded'
ELSE PRINT 'Procedure Creation: addPermission Error on Creation'
GO
/****** Object: StoredProcedure [deletePermission] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deletePermission]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deletePermission];
GO
CREATE PROCEDURE [dbo].[deletePermission]
(
@PID int
)
AS
DELETE [Permissions]
WHERE [PID] = @PID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deletePermission Succeeded'
ELSE PRINT 'Procedure Creation: deletePermission Error on Creation'
GO
/****** Object: StoredProcedure [getPermission] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPermission]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getPermission];
GO
CREATE PROCEDURE [dbo].[getPermission]
(
@PID int
)
AS
SELECT
[PID],
[RID],
[PermLevel],
[VersionType],
[PermValue],
[PermAD],
[StartDate],
[EndDate],
[Config],
[DTS],
[UsrID],
[LastChanged]
FROM [Permissions]
WHERE [PID]=@PID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPermission Succeeded'
ELSE PRINT 'Procedure Creation: getPermission Error on Creation'
GO
/****** Object: StoredProcedure [existsPermission] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsPermission]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsPermission];
GO
CREATE PROCEDURE [dbo].[existsPermission]
(
@PID int
)
AS
SELECT COUNT(*)
FROM [Permissions] WHERE [PID]=@PID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsPermission Succeeded'
ELSE PRINT 'Procedure Creation: existsPermission Error on Creation'
GO
/****** Object: StoredProcedure [getPermissionsByRID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getPermissionsByRID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getPermissionsByRID];
GO
CREATE PROCEDURE [dbo].[getPermissionsByRID]
(
@RID int
)
AS
SELECT
[Permissions].[PID],
[Permissions].[RID],
[Permissions].[PermLevel],
[Permissions].[VersionType],
[Permissions].[PermValue],
[Permissions].[PermAD],
[Permissions].[StartDate],
[Permissions].[EndDate],
[Permissions].[Config],
[Permissions].[DTS],
[Permissions].[UsrID],
[Permissions].[LastChanged]
FROM [Permissions]
WHERE
[Permissions].[RID]=@RID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getPermissionsByRID Succeeded'
ELSE PRINT 'Procedure Creation: getPermissionsByRID Error on Creation'
GO
/****** Object: StoredProcedure [getRODbs] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRODbs]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRODbs];
GO
CREATE PROCEDURE [dbo].[getRODbs]
AS
SELECT
[RODbID],
[ROName],
[FolderPath],
[DBConnectionString],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [ROFsts] WHERE [ROFsts].[RODbID]=[RODbs].[RODbID]) [ROFstCount],
(SELECT COUNT(*) FROM [ROImages] WHERE [ROImages].[RODbID]=[RODbs].[RODbID]) [ROImageCount],
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[RODbID]=[RODbs].[RODbID]) [RoUsageCount]
FROM [RODbs]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRODbs Succeeded'
ELSE PRINT 'Procedure Creation: getRODbs Error on Creation'
GO
/****** Object: StoredProcedure [updateRODb] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateRODb]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateRODb];
GO
CREATE PROCEDURE [dbo].[updateRODb]
(
@RODbID int,
@ROName nvarchar(255),
@FolderPath nvarchar(255),
@DBConnectionString nvarchar(MAX),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [RODbs]
SET
[ROName]=@ROName,
[FolderPath]=@FolderPath,
[DBConnectionString]=@DBConnectionString,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [RODbID]=@RODbID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [RODbs] WHERE [RODbID]=@RODbID)
RAISERROR('RODb record has been deleted by another user', 16, 1)
ELSE
RAISERROR('RODb has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [RODbs] WHERE [RODbID]=@RODbID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateRODb Succeeded'
ELSE PRINT 'Procedure Creation: updateRODb Error on Creation'
GO
/****** Object: StoredProcedure [addRODb] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addRODb]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addRODb];
GO
CREATE PROCEDURE [dbo].[addRODb]
(
@ROName nvarchar(255),
@FolderPath nvarchar(255),
@DBConnectionString nvarchar(MAX),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newRODbID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [RODbs]
(
[ROName],
[FolderPath],
[DBConnectionString],
[Config],
[DTS],
[UserID]
)
VALUES
(
@ROName,
@FolderPath,
@DBConnectionString,
@Config,
@DTS,
@UserID
)
SELECT @newRODbID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [RODbs] WHERE [RODbID]=@newRODbID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addRODb Succeeded'
ELSE PRINT 'Procedure Creation: addRODb Error on Creation'
GO
/****** Object: StoredProcedure [deleteRODb] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteRODb]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteRODb];
GO
CREATE PROCEDURE [dbo].[deleteRODb]
(
@RODbID int
)
AS
DELETE [ROFsts]
WHERE [RODbID]=@RODbID
DELETE [ROImages]
WHERE [RODbID]=@RODbID
DELETE [RoUsages]
WHERE [RODbID]=@RODbID
DELETE [RODbs]
WHERE [RODbID] = @RODbID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteRODb Succeeded'
ELSE PRINT 'Procedure Creation: deleteRODb Error on Creation'
GO
/****** Object: StoredProcedure [getRODb] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRODb]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRODb];
GO
CREATE PROCEDURE [dbo].[getRODb]
(
@RODbID int
)
AS
SELECT
[RODbID],
[ROName],
[FolderPath],
[DBConnectionString],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [ROFsts] WHERE [ROFsts].[RODbID]=[RODbs].[RODbID]) [ROFstCount],
(SELECT COUNT(*) FROM [ROImages] WHERE [ROImages].[RODbID]=[RODbs].[RODbID]) [ROImageCount],
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[RODbID]=[RODbs].[RODbID]) [RoUsageCount]
FROM [RODbs]
WHERE [RODbID]=@RODbID
SELECT
[ROFsts].[ROFstID],
[ROFsts].[RODbID],
[ROFsts].[ROLookup],
[ROFsts].[Config],
[ROFsts].[DTS],
[ROFsts].[UserID],
[ROFsts].[LastChanged]
FROM [ROFsts]
WHERE
[ROFsts].[RODbID]=@RODbID
SELECT
[ROImages].[ImageID],
[ROImages].[RODbID],
[ROImages].[FileName],
[ROImages].[Content],
[ROImages].[Config],
[ROImages].[DTS],
[ROImages].[UserID],
[ROImages].[LastChanged]
FROM [ROImages]
WHERE
[ROImages].[RODbID]=@RODbID
SELECT
[RoUsages].[ROUsageID],
[RoUsages].[ContentID],
[RoUsages].[ROID],
[RoUsages].[Config],
[RoUsages].[DTS],
[RoUsages].[UserID],
[RoUsages].[LastChanged],
[RoUsages].[RODbID],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID]
FROM [RoUsages]
JOIN [Contents] ON
[Contents].[ContentID]=[RoUsages].[ContentID]
WHERE
[RoUsages].[RODbID]=@RODbID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRODb Succeeded'
ELSE PRINT 'Procedure Creation: getRODb Error on Creation'
GO
/****** Object: StoredProcedure [getRODbByFolderPath] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRODbByFolderPath]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRODbByFolderPath];
GO
CREATE PROCEDURE [dbo].[getRODbByFolderPath]
(
@FolderPath nvarchar(255)
)
AS
SELECT
[RODbID],
[ROName],
[FolderPath],
[DBConnectionString],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [ROFsts] WHERE [ROFsts].[RODbID]=[RODbs].[RODbID]) [ROFstCount],
(SELECT COUNT(*) FROM [ROImages] WHERE [ROImages].[RODbID]=[RODbs].[RODbID]) [ROImageCount],
(SELECT COUNT(*) FROM [RoUsages] WHERE [RoUsages].[RODbID]=[RODbs].[RODbID]) [RoUsageCount]
FROM [RODbs]
WHERE [FolderPath]=@FolderPath
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRODbByFolderPath Succeeded'
ELSE PRINT 'Procedure Creation: getRODbByFolderPath Error on Creation'
GO
/****** Object: StoredProcedure [existsRODb] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsRODb]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsRODb];
GO
CREATE PROCEDURE [dbo].[existsRODb]
(
@RODbID int
)
AS
SELECT COUNT(*)
FROM [RODbs] WHERE [RODbID]=@RODbID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsRODb Succeeded'
ELSE PRINT 'Procedure Creation: existsRODb Error on Creation'
GO
/****** Object: StoredProcedure [getROFsts] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROFsts]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getROFsts];
GO
CREATE PROCEDURE [dbo].[getROFsts]
AS
SELECT
[ROFstID],
[RODbID],
[ROLookup],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount],
(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount]
FROM [ROFsts]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROFsts Succeeded'
ELSE PRINT 'Procedure Creation: getROFsts Error on Creation'
GO
/****** Object: StoredProcedure [updateROFst] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateROFst]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateROFst];
GO
CREATE PROCEDURE [dbo].[updateROFst]
(
@ROFstID int,
@RODbID int,
@ROLookup varbinary(MAX),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [ROFsts]
SET
[RODbID]=@RODbID,
[ROLookup]=@ROLookup,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [ROFstID]=@ROFstID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [ROFsts] WHERE [ROFstID]=@ROFstID)
RAISERROR('ROFst record has been deleted by another user', 16, 1)
ELSE
RAISERROR('ROFst has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [ROFsts] WHERE [ROFstID]=@ROFstID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateROFst Succeeded'
ELSE PRINT 'Procedure Creation: updateROFst Error on Creation'
GO
/****** Object: StoredProcedure [addROFst] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addROFst]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addROFst];
GO
CREATE PROCEDURE [dbo].[addROFst]
(
@RODbID int,
@ROLookup varbinary(MAX),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newROFstID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [ROFsts]
(
[RODbID],
[ROLookup],
[Config],
[DTS],
[UserID]
)
VALUES
(
@RODbID,
@ROLookup,
@Config,
@DTS,
@UserID
)
SELECT @newROFstID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [ROFsts] WHERE [ROFstID]=@newROFstID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addROFst Succeeded'
ELSE PRINT 'Procedure Creation: addROFst Error on Creation'
GO
/****** Object: StoredProcedure [deleteROFst] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteROFst]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteROFst];
GO
CREATE PROCEDURE [dbo].[deleteROFst]
(
@ROFstID int
)
AS
DELETE [Associations]
WHERE [ROFstID]=@ROFstID
DELETE [Figures]
WHERE [ROFstID]=@ROFstID
DELETE [ROFsts]
WHERE [ROFstID] = @ROFstID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteROFst Succeeded'
ELSE PRINT 'Procedure Creation: deleteROFst Error on Creation'
GO
/****** Object: StoredProcedure [getROFst] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROFst]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getROFst];
GO
CREATE PROCEDURE [dbo].[getROFst]
(
@ROFstID int
)
AS
SELECT
[ROFstID],
[RODbID],
[ROLookup],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount],
(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount]
FROM [ROFsts]
WHERE [ROFstID]=@ROFstID
SELECT
[Associations].[AssociationID],
[Associations].[VersionID],
[Associations].[ROFstID],
[Associations].[Config],
[Associations].[DTS],
[Associations].[UserID],
[Associations].[LastChanged],
[DocVersions].[FolderID] [DocVersion_FolderID],
[DocVersions].[VersionType] [DocVersion_VersionType],
[DocVersions].[Name] [DocVersion_Name],
[DocVersions].[Title] [DocVersion_Title],
[DocVersions].[ItemID] [DocVersion_ItemID],
[DocVersions].[FormatID] [DocVersion_FormatID],
[DocVersions].[Config] [DocVersion_Config],
[DocVersions].[DTS] [DocVersion_DTS],
[DocVersions].[UserID] [DocVersion_UserID]
FROM [Associations]
JOIN [DocVersions] ON
[DocVersions].[VersionID]=[Associations].[VersionID]
WHERE
[Associations].[ROFstID]=@ROFstID
SELECT
[Figures].[FigureID],
[Figures].[ROFstID],
[Figures].[ImageID],
[Figures].[Config],
[Figures].[DTS],
[Figures].[UserID],
[Figures].[LastChanged],
[ROImages].[RODbID] [ROImage_RODbID],
[ROImages].[FileName] [ROImage_FileName],
[ROImages].[Content] [ROImage_Content],
[ROImages].[Config] [ROImage_Config],
[ROImages].[DTS] [ROImage_DTS],
[ROImages].[UserID] [ROImage_UserID]
FROM [Figures]
JOIN [ROImages] ON
[ROImages].[ImageID]=[Figures].[ImageID]
WHERE
[Figures].[ROFstID]=@ROFstID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROFst Succeeded'
ELSE PRINT 'Procedure Creation: getROFst Error on Creation'
GO
/****** Object: StoredProcedure [getROFstByRODbID_DTS] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROFstByRODbID_DTS]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getROFstByRODbID_DTS];
GO
CREATE PROCEDURE [dbo].[getROFstByRODbID_DTS]
(
@RODbID int,
@DTS datetime
)
AS
SELECT
[ROFstID],
[RODbID],
[ROLookup],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount],
(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount]
FROM [ROFsts]
WHERE [RODbID]=@RODbID AND [DTS]=@DTS
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROFstByRODbID_DTS Succeeded'
ELSE PRINT 'Procedure Creation: getROFstByRODbID_DTS Error on Creation'
GO
/****** Object: StoredProcedure [existsROFst] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsROFst]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsROFst];
GO
CREATE PROCEDURE [dbo].[existsROFst]
(
@ROFstID int
)
AS
SELECT COUNT(*)
FROM [ROFsts] WHERE [ROFstID]=@ROFstID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsROFst Succeeded'
ELSE PRINT 'Procedure Creation: existsROFst Error on Creation'
GO
/****** Object: StoredProcedure [getROFstsByRODbID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROFstsByRODbID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getROFstsByRODbID];
GO
CREATE PROCEDURE [dbo].[getROFstsByRODbID]
(
@RODbID int
)
AS
SELECT
[ROFsts].[ROFstID],
[ROFsts].[RODbID],
[ROFsts].[ROLookup],
[ROFsts].[Config],
[ROFsts].[DTS],
[ROFsts].[UserID],
[ROFsts].[LastChanged],
(SELECT COUNT(*) FROM [Associations] WHERE [Associations].[ROFstID]=[ROFsts].[ROFstID]) [AssociationCount],
(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ROFstID]=[ROFsts].[ROFstID]) [FigureCount]
FROM [ROFsts]
WHERE
[ROFsts].[RODbID]=@RODbID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROFstsByRODbID Succeeded'
ELSE PRINT 'Procedure Creation: getROFstsByRODbID Error on Creation'
GO
/****** Object: StoredProcedure [getROImages] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROImages]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getROImages];
GO
CREATE PROCEDURE [dbo].[getROImages]
AS
SELECT
[ImageID],
[RODbID],
[FileName],
[Content],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ImageID]=[ROImages].[ImageID]) [FigureCount]
FROM [ROImages]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROImages Succeeded'
ELSE PRINT 'Procedure Creation: getROImages Error on Creation'
GO
/****** Object: StoredProcedure [updateROImage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateROImage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateROImage];
GO
CREATE PROCEDURE [dbo].[updateROImage]
(
@ImageID int,
@RODbID int,
@FileName nvarchar(255),
@Content varbinary(MAX),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [ROImages]
SET
[RODbID]=@RODbID,
[FileName]=@FileName,
[Content]=@Content,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [ImageID]=@ImageID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [ROImages] WHERE [ImageID]=@ImageID)
RAISERROR('ROImage record has been deleted by another user', 16, 1)
ELSE
RAISERROR('ROImage has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [ROImages] WHERE [ImageID]=@ImageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateROImage Succeeded'
ELSE PRINT 'Procedure Creation: updateROImage Error on Creation'
GO
/****** Object: StoredProcedure [addROImage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addROImage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addROImage];
GO
CREATE PROCEDURE [dbo].[addROImage]
(
@RODbID int,
@FileName nvarchar(255),
@Content varbinary(MAX),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newImageID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [ROImages]
(
[RODbID],
[FileName],
[Content],
[Config],
[DTS],
[UserID]
)
VALUES
(
@RODbID,
@FileName,
@Content,
@Config,
@DTS,
@UserID
)
SELECT @newImageID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [ROImages] WHERE [ImageID]=@newImageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addROImage Succeeded'
ELSE PRINT 'Procedure Creation: addROImage Error on Creation'
GO
/****** Object: StoredProcedure [deleteROImage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteROImage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteROImage];
GO
CREATE PROCEDURE [dbo].[deleteROImage]
(
@ImageID int
)
AS
DELETE [Figures]
WHERE [ImageID]=@ImageID
DELETE [ROImages]
WHERE [ImageID] = @ImageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteROImage Succeeded'
ELSE PRINT 'Procedure Creation: deleteROImage Error on Creation'
GO
/****** Object: StoredProcedure [getROImage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROImage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getROImage];
GO
CREATE PROCEDURE [dbo].[getROImage]
(
@ImageID int
)
AS
SELECT
[ImageID],
[RODbID],
[FileName],
[Content],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ImageID]=[ROImages].[ImageID]) [FigureCount]
FROM [ROImages]
WHERE [ImageID]=@ImageID
SELECT
[Figures].[FigureID],
[Figures].[ROFstID],
[Figures].[ImageID],
[Figures].[Config],
[Figures].[DTS],
[Figures].[UserID],
[Figures].[LastChanged],
[ROFsts].[RODbID] [ROFst_RODbID],
[ROFsts].[ROLookup] [ROFst_ROLookup],
[ROFsts].[Config] [ROFst_Config],
[ROFsts].[DTS] [ROFst_DTS],
[ROFsts].[UserID] [ROFst_UserID]
FROM [Figures]
JOIN [ROFsts] ON
[ROFsts].[ROFstID]=[Figures].[ROFstID]
WHERE
[Figures].[ImageID]=@ImageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROImage Succeeded'
ELSE PRINT 'Procedure Creation: getROImage Error on Creation'
GO
/****** Object: StoredProcedure [getROImageByRODbID_FileName_DTS] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROImageByRODbID_FileName_DTS]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getROImageByRODbID_FileName_DTS];
GO
CREATE PROCEDURE [dbo].[getROImageByRODbID_FileName_DTS]
(
@RODbID int,
@FileName nvarchar(255),
@DTS datetime
)
AS
SELECT
[ImageID],
[RODbID],
[FileName],
[Content],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ImageID]=[ROImages].[ImageID]) [FigureCount]
FROM [ROImages]
WHERE [RODbID]=@RODbID AND [FileName]=@FileName AND [DTS]=@DTS
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROImageByRODbID_FileName_DTS Succeeded'
ELSE PRINT 'Procedure Creation: getROImageByRODbID_FileName_DTS Error on Creation'
GO
/****** Object: StoredProcedure [existsROImage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsROImage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsROImage];
GO
CREATE PROCEDURE [dbo].[existsROImage]
(
@ImageID int
)
AS
SELECT COUNT(*)
FROM [ROImages] WHERE [ImageID]=@ImageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsROImage Succeeded'
ELSE PRINT 'Procedure Creation: existsROImage Error on Creation'
GO
/****** Object: StoredProcedure [getROImagesByRODbID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getROImagesByRODbID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getROImagesByRODbID];
GO
CREATE PROCEDURE [dbo].[getROImagesByRODbID]
(
@RODbID int
)
AS
SELECT
[ROImages].[ImageID],
[ROImages].[RODbID],
[ROImages].[FileName],
[ROImages].[Content],
[ROImages].[Config],
[ROImages].[DTS],
[ROImages].[UserID],
[ROImages].[LastChanged],
(SELECT COUNT(*) FROM [Figures] WHERE [Figures].[ImageID]=[ROImages].[ImageID]) [FigureCount]
FROM [ROImages]
WHERE
[ROImages].[RODbID]=@RODbID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getROImagesByRODbID Succeeded'
ELSE PRINT 'Procedure Creation: getROImagesByRODbID Error on Creation'
GO
/****** Object: StoredProcedure [getRoles] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRoles]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRoles];
GO
CREATE PROCEDURE [dbo].[getRoles]
AS
SELECT
[RID],
[Name],
[Title],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[RID]=[Roles].[RID]) [AssignmentCount],
(SELECT COUNT(*) FROM [Permissions] WHERE [Permissions].[RID]=[Roles].[RID]) [PermissionCount]
FROM [Roles]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoles Succeeded'
ELSE PRINT 'Procedure Creation: getRoles Error on Creation'
GO
/****** Object: StoredProcedure [updateRole] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateRole];
GO
CREATE PROCEDURE [dbo].[updateRole]
(
@RID int,
@Name nvarchar(50),
@Title nvarchar(250),
@DTS datetime,
@UsrID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Roles]
SET
[Name]=@Name,
[Title]=@Title,
[DTS]=@DTS,
[UsrID]=@UsrID
WHERE [RID]=@RID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Roles] WHERE [RID]=@RID)
RAISERROR('Role record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Role has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Roles] WHERE [RID]=@RID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateRole Succeeded'
ELSE PRINT 'Procedure Creation: updateRole Error on Creation'
GO
/****** Object: StoredProcedure [addRole] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addRole];
GO
CREATE PROCEDURE [dbo].[addRole]
(
@Name nvarchar(50),
@Title nvarchar(250),
@DTS datetime,
@UsrID nvarchar(100),
@newRID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Roles]
(
[Name],
[Title],
[DTS],
[UsrID]
)
VALUES
(
@Name,
@Title,
@DTS,
@UsrID
)
SELECT @newRID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Roles] WHERE [RID]=@newRID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addRole Succeeded'
ELSE PRINT 'Procedure Creation: addRole Error on Creation'
GO
/****** Object: StoredProcedure [deleteRole] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteRole];
GO
CREATE PROCEDURE [dbo].[deleteRole]
(
@RID int
)
AS
DELETE [Assignments]
WHERE [RID]=@RID
DELETE [Permissions]
WHERE [RID]=@RID
DELETE [Roles]
WHERE [RID] = @RID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteRole Succeeded'
ELSE PRINT 'Procedure Creation: deleteRole Error on Creation'
GO
/****** Object: StoredProcedure [getRole] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRole];
GO
CREATE PROCEDURE [dbo].[getRole]
(
@RID int
)
AS
SELECT
[RID],
[Name],
[Title],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[RID]=[Roles].[RID]) [AssignmentCount],
(SELECT COUNT(*) FROM [Permissions] WHERE [Permissions].[RID]=[Roles].[RID]) [PermissionCount]
FROM [Roles]
WHERE [RID]=@RID
SELECT
[Assignments].[AID],
[Assignments].[GID],
[Assignments].[RID],
[Assignments].[FolderID],
[Assignments].[StartDate],
[Assignments].[EndDate],
[Assignments].[DTS],
[Assignments].[UsrID],
[Assignments].[LastChanged],
[Folders].[ParentID] [Folder_ParentID],
[Folders].[DBID] [Folder_DBID],
[Folders].[Name] [Folder_Name],
[Folders].[Title] [Folder_Title],
[Folders].[ShortName] [Folder_ShortName],
[Folders].[FormatID] [Folder_FormatID],
[Folders].[ManualOrder] [Folder_ManualOrder],
[Folders].[Config] [Folder_Config],
[Folders].[DTS] [Folder_DTS],
[Folders].[UsrID] [Folder_UsrID],
[Groups].[GroupName] [Group_GroupName],
[Groups].[GroupType] [Group_GroupType],
[Groups].[Config] [Group_Config],
[Groups].[DTS] [Group_DTS],
[Groups].[UsrID] [Group_UsrID]
FROM [Assignments]
JOIN [Folders] ON
[Folders].[FolderID]=[Assignments].[FolderID]
JOIN [Groups] ON
[Groups].[GID]=[Assignments].[GID]
WHERE
[Assignments].[RID]=@RID
SELECT
[Permissions].[PID],
[Permissions].[RID],
[Permissions].[PermLevel],
[Permissions].[VersionType],
[Permissions].[PermValue],
[Permissions].[PermAD],
[Permissions].[StartDate],
[Permissions].[EndDate],
[Permissions].[Config],
[Permissions].[DTS],
[Permissions].[UsrID],
[Permissions].[LastChanged]
FROM [Permissions]
WHERE
[Permissions].[RID]=@RID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRole Succeeded'
ELSE PRINT 'Procedure Creation: getRole Error on Creation'
GO
/****** Object: StoredProcedure [getRoleByName] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRoleByName]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRoleByName];
GO
CREATE PROCEDURE [dbo].[getRoleByName]
(
@Name nvarchar(50)
)
AS
SELECT
[RID],
[Name],
[Title],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Assignments] WHERE [Assignments].[RID]=[Roles].[RID]) [AssignmentCount],
(SELECT COUNT(*) FROM [Permissions] WHERE [Permissions].[RID]=[Roles].[RID]) [PermissionCount]
FROM [Roles]
WHERE [Name]=@Name
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoleByName Succeeded'
ELSE PRINT 'Procedure Creation: getRoleByName Error on Creation'
GO
/****** Object: StoredProcedure [existsRole] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsRole]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsRole];
GO
CREATE PROCEDURE [dbo].[existsRole]
(
@RID int
)
AS
SELECT COUNT(*)
FROM [Roles] WHERE [RID]=@RID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsRole Succeeded'
ELSE PRINT 'Procedure Creation: existsRole Error on Creation'
GO
/****** Object: StoredProcedure [getRoUsages] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRoUsages]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRoUsages];
GO
CREATE PROCEDURE [dbo].[getRoUsages]
AS
SELECT
[ROUsageID],
[ContentID],
[ROID],
[Config],
[DTS],
[UserID],
[LastChanged],
[RODbID]
FROM [RoUsages]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoUsages Succeeded'
ELSE PRINT 'Procedure Creation: getRoUsages Error on Creation'
GO
/****** Object: StoredProcedure [updateRoUsage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateRoUsage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateRoUsage];
GO
CREATE PROCEDURE [dbo].[updateRoUsage]
(
@ROUsageID int,
@ContentID int,
@ROID nvarchar(16),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@RODbID int,
@newLastChanged timestamp output
)
AS
UPDATE [RoUsages]
SET
[ContentID]=@ContentID,
[ROID]=@ROID,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID,
[RODbID]=@RODbID
WHERE [ROUsageID]=@ROUsageID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [RoUsages] WHERE [ROUsageID]=@ROUsageID)
RAISERROR('RoUsage record has been deleted by another user', 16, 1)
ELSE
RAISERROR('RoUsage has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [RoUsages] WHERE [ROUsageID]=@ROUsageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: updateRoUsage Error on Creation'
GO
/****** Object: StoredProcedure [addRoUsage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addRoUsage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addRoUsage];
GO
CREATE PROCEDURE [dbo].[addRoUsage]
(
@ContentID int,
@ROID nvarchar(16),
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@RODbID int,
@newROUsageID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [RoUsages]
(
[ContentID],
[ROID],
[Config],
[DTS],
[UserID],
[RODbID]
)
VALUES
(
@ContentID,
@ROID,
@Config,
@DTS,
@UserID,
@RODbID
)
SELECT @newROUsageID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [RoUsages] WHERE [ROUsageID]=@newROUsageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: addRoUsage Error on Creation'
GO
/****** Object: StoredProcedure [deleteRoUsage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteRoUsage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteRoUsage];
GO
CREATE PROCEDURE [dbo].[deleteRoUsage]
(
@ROUsageID int
)
AS
DELETE [RoUsages]
WHERE [ROUsageID] = @ROUsageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: deleteRoUsage Error on Creation'
GO
/****** Object: StoredProcedure [getRoUsage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRoUsage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRoUsage];
GO
CREATE PROCEDURE [dbo].[getRoUsage]
(
@ROUsageID int
)
AS
SELECT
[ROUsageID],
[ContentID],
[ROID],
[Config],
[DTS],
[UserID],
[LastChanged],
[RODbID]
FROM [RoUsages]
WHERE [ROUsageID]=@ROUsageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: getRoUsage Error on Creation'
GO
/****** Object: StoredProcedure [existsRoUsage] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsRoUsage]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsRoUsage];
GO
CREATE PROCEDURE [dbo].[existsRoUsage]
(
@ROUsageID int
)
AS
SELECT COUNT(*)
FROM [RoUsages] WHERE [ROUsageID]=@ROUsageID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsRoUsage Succeeded'
ELSE PRINT 'Procedure Creation: existsRoUsage Error on Creation'
GO
/****** Object: StoredProcedure [getRoUsagesByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRoUsagesByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRoUsagesByContentID];
GO
CREATE PROCEDURE [dbo].[getRoUsagesByContentID]
(
@ContentID int
)
AS
SELECT
[RoUsages].[ROUsageID],
[RoUsages].[ContentID],
[RoUsages].[ROID],
[RoUsages].[Config],
[RoUsages].[DTS],
[RoUsages].[UserID],
[RoUsages].[LastChanged],
[RoUsages].[RODbID],
[RODbs].[ROName] [RODb_ROName],
[RODbs].[FolderPath] [RODb_FolderPath],
[RODbs].[DBConnectionString] [RODb_DBConnectionString],
[RODbs].[Config] [RODb_Config],
[RODbs].[DTS] [RODb_DTS],
[RODbs].[UserID] [RODb_UserID]
FROM [RoUsages]
JOIN [RODbs] ON
[RODbs].[RODbID]=[RoUsages].[RODbID]
WHERE
[RoUsages].[ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoUsagesByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getRoUsagesByContentID Error on Creation'
GO
/****** Object: StoredProcedure [getRoUsagesByRODbID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRoUsagesByRODbID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getRoUsagesByRODbID];
GO
CREATE PROCEDURE [dbo].[getRoUsagesByRODbID]
(
@RODbID int
)
AS
SELECT
[RoUsages].[ROUsageID],
[RoUsages].[ContentID],
[RoUsages].[ROID],
[RoUsages].[Config],
[RoUsages].[DTS],
[RoUsages].[UserID],
[RoUsages].[LastChanged],
[RoUsages].[RODbID],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID]
FROM [RoUsages]
JOIN [Contents] ON
[Contents].[ContentID]=[RoUsages].[ContentID]
WHERE
[RoUsages].[RODbID]=@RODbID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRoUsagesByRODbID Succeeded'
ELSE PRINT 'Procedure Creation: getRoUsagesByRODbID Error on Creation'
GO
/****** Object: StoredProcedure [getTransitions] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getTransitions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getTransitions];
GO
CREATE PROCEDURE [dbo].[getTransitions]
AS
SELECT
[TransitionID],
[FromID],
[ToID],
[RangeID],
[TranType],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
FROM [Transitions]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getTransitions Succeeded'
ELSE PRINT 'Procedure Creation: getTransitions Error on Creation'
GO
/****** Object: StoredProcedure [updateTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateTransition];
GO
CREATE PROCEDURE [dbo].[updateTransition]
(
@TransitionID int,
@FromID int,
@ToID int,
@RangeID int,
@TranType int,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Transitions]
SET
[FromID]=@FromID,
[ToID]=@ToID,
[RangeID]=@RangeID,
[TranType]=@TranType,
[Config]=@Config,
[DTS]=@DTS,
[UserID]=@UserID
WHERE [TransitionID]=@TransitionID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Transitions] WHERE [TransitionID]=@TransitionID)
RAISERROR('Transition record has been deleted by another user', 16, 1)
ELSE
RAISERROR('Transition has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Transitions] WHERE [TransitionID]=@TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateTransition Succeeded'
ELSE PRINT 'Procedure Creation: updateTransition Error on Creation'
GO
/****** Object: StoredProcedure [addTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addTransition];
GO
CREATE PROCEDURE [dbo].[addTransition]
(
@FromID int,
@ToID int,
@RangeID int,
@TranType int,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UserID nvarchar(100),
@newTransitionID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Transitions]
(
[FromID],
[ToID],
[RangeID],
[TranType],
[Config],
[DTS],
[UserID]
)
VALUES
(
@FromID,
@ToID,
@RangeID,
@TranType,
@Config,
@DTS,
@UserID
)
SELECT @newTransitionID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Transitions] WHERE [TransitionID]=@newTransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addTransition Succeeded'
ELSE PRINT 'Procedure Creation: addTransition Error on Creation'
GO
/****** Object: StoredProcedure [deleteTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteTransition];
GO
CREATE PROCEDURE [dbo].[deleteTransition]
(
@TransitionID int
)
AS
DELETE [ZTransitions]
WHERE [TransitionID]=@TransitionID
DELETE [Transitions]
WHERE [TransitionID] = @TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteTransition Succeeded'
ELSE PRINT 'Procedure Creation: deleteTransition Error on Creation'
GO
/****** Object: StoredProcedure [getTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getTransition];
GO
CREATE PROCEDURE [dbo].[getTransition]
(
@TransitionID int
)
AS
SELECT
[TransitionID],
[FromID],
[ToID],
[RangeID],
[TranType],
[Config],
[DTS],
[UserID],
[LastChanged],
(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
FROM [Transitions]
WHERE [TransitionID]=@TransitionID
SELECT
[ZTransitions].[TransitionID],
[ZTransitions].[oldto],
[ZTransitions].[LastChanged]
FROM [ZTransitions]
WHERE
[ZTransitions].[TransitionID]=@TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getTransition Succeeded'
ELSE PRINT 'Procedure Creation: getTransition Error on Creation'
GO
/****** Object: StoredProcedure [existsTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsTransition];
GO
CREATE PROCEDURE [dbo].[existsTransition]
(
@TransitionID int
)
AS
SELECT COUNT(*)
FROM [Transitions] WHERE [TransitionID]=@TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsTransition Succeeded'
ELSE PRINT 'Procedure Creation: existsTransition Error on Creation'
GO
/****** Object: StoredProcedure [getTransitionsByFromID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getTransitionsByFromID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getTransitionsByFromID];
GO
CREATE PROCEDURE [dbo].[getTransitionsByFromID]
(
@FromID int
)
AS
SELECT
[Transitions].[TransitionID],
[Transitions].[FromID],
[Transitions].[ToID],
[Transitions].[RangeID],
[Transitions].[TranType],
[Transitions].[Config],
[Transitions].[DTS],
[Transitions].[UserID],
[Transitions].[LastChanged],
[Items_RangeID].[PreviousID] [Item_RangeID_PreviousID],
[Items_RangeID].[ContentID] [Item_RangeID_ContentID],
[Items_RangeID].[DTS] [Item_RangeID_DTS],
[Items_RangeID].[UserID] [Item_RangeID_UserID],
[Items_ToID].[PreviousID] [Item_ToID_PreviousID],
[Items_ToID].[ContentID] [Item_ToID_ContentID],
[Items_ToID].[DTS] [Item_ToID_DTS],
[Items_ToID].[UserID] [Item_ToID_UserID],
(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
FROM [Transitions]
JOIN [Items] [Items_RangeID] ON
[Items_RangeID].[ItemID]=[Transitions].[RangeID]
JOIN [Items] [Items_ToID] ON
[Items_ToID].[ItemID]=[Transitions].[ToID]
WHERE
[Transitions].[FromID]=@FromID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getTransitionsByFromID Succeeded'
ELSE PRINT 'Procedure Creation: getTransitionsByFromID Error on Creation'
GO
/****** Object: StoredProcedure [getTransitionsByRangeID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getTransitionsByRangeID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getTransitionsByRangeID];
GO
CREATE PROCEDURE [dbo].[getTransitionsByRangeID]
(
@RangeID int
)
AS
SELECT
[Transitions].[TransitionID],
[Transitions].[FromID],
[Transitions].[ToID],
[Transitions].[RangeID],
[Transitions].[TranType],
[Transitions].[Config],
[Transitions].[DTS],
[Transitions].[UserID],
[Transitions].[LastChanged],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID],
(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
FROM [Transitions]
JOIN [Contents] ON
[Contents].[ContentID]=[Transitions].[FromID]
WHERE
[Transitions].[RangeID]=@RangeID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getTransitionsByRangeID Succeeded'
ELSE PRINT 'Procedure Creation: getTransitionsByRangeID Error on Creation'
GO
/****** Object: StoredProcedure [getTransitionsByToID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getTransitionsByToID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getTransitionsByToID];
GO
CREATE PROCEDURE [dbo].[getTransitionsByToID]
(
@ToID int
)
AS
SELECT
[Transitions].[TransitionID],
[Transitions].[FromID],
[Transitions].[ToID],
[Transitions].[RangeID],
[Transitions].[TranType],
[Transitions].[Config],
[Transitions].[DTS],
[Transitions].[UserID],
[Transitions].[LastChanged],
[Contents].[Number] [Content_Number],
[Contents].[Text] [Content_Text],
[Contents].[Type] [Content_Type],
[Contents].[FormatID] [Content_FormatID],
[Contents].[Config] [Content_Config],
[Contents].[DTS] [Content_DTS],
[Contents].[UserID] [Content_UserID],
(SELECT COUNT(*) FROM [ZTransitions] WHERE [ZTransitions].[TransitionID]=[Transitions].[TransitionID]) [ZTransitionCount]
FROM [Transitions]
JOIN [Contents] ON
[Contents].[ContentID]=[Transitions].[FromID]
WHERE
[Transitions].[ToID]=@ToID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getTransitionsByToID Succeeded'
ELSE PRINT 'Procedure Creation: getTransitionsByToID Error on Creation'
GO
/****** Object: StoredProcedure [getUsers] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getUsers]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getUsers];
GO
CREATE PROCEDURE [dbo].[getUsers]
AS
SELECT
[UID],
[UserID],
[FirstName],
[MiddleName],
[LastName],
[Suffix],
[CourtesyTitle],
[PhoneNumber],
[CFGName],
[UserLogin],
[UserName],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[UID]=[Users].[UID]) [MembershipCount]
FROM [Users]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getUsers Succeeded'
ELSE PRINT 'Procedure Creation: getUsers Error on Creation'
GO
/****** Object: StoredProcedure [updateUser] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateUser];
GO
CREATE PROCEDURE [dbo].[updateUser]
(
@UID int,
@UserID nvarchar(100),
@FirstName nvarchar(50)=null,
@MiddleName nvarchar(50)=null,
@LastName nvarchar(50)=null,
@Suffix nvarchar(10)=null,
@CourtesyTitle nvarchar(10)=null,
@PhoneNumber nvarchar(30)=null,
@CFGName nvarchar(8)=null,
@UserLogin nvarchar(10)=null,
@UserName nvarchar(32)=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [Users]
SET
[UserID]=@UserID,
[FirstName]=@FirstName,
[MiddleName]=@MiddleName,
[LastName]=@LastName,
[Suffix]=@Suffix,
[CourtesyTitle]=@CourtesyTitle,
[PhoneNumber]=@PhoneNumber,
[CFGName]=@CFGName,
[UserLogin]=@UserLogin,
[UserName]=@UserName,
[Config]=@Config,
[DTS]=@DTS,
[UsrID]=@UsrID
WHERE [UID]=@UID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [Users] WHERE [UID]=@UID)
RAISERROR('User record has been deleted by another user', 16, 1)
ELSE
RAISERROR('User has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [Users] WHERE [UID]=@UID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateUser Succeeded'
ELSE PRINT 'Procedure Creation: updateUser Error on Creation'
GO
/****** Object: StoredProcedure [addUser] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addUser];
GO
CREATE PROCEDURE [dbo].[addUser]
(
@UserID nvarchar(100),
@FirstName nvarchar(50)=null,
@MiddleName nvarchar(50)=null,
@LastName nvarchar(50)=null,
@Suffix nvarchar(10)=null,
@CourtesyTitle nvarchar(10)=null,
@PhoneNumber nvarchar(30)=null,
@CFGName nvarchar(8)=null,
@UserLogin nvarchar(10)=null,
@UserName nvarchar(32)=null,
@Config nvarchar(MAX)=null,
@DTS datetime,
@UsrID nvarchar(100),
@newUID int output,
@newLastChanged timestamp output
)
AS
INSERT INTO [Users]
(
[UserID],
[FirstName],
[MiddleName],
[LastName],
[Suffix],
[CourtesyTitle],
[PhoneNumber],
[CFGName],
[UserLogin],
[UserName],
[Config],
[DTS],
[UsrID]
)
VALUES
(
@UserID,
@FirstName,
@MiddleName,
@LastName,
@Suffix,
@CourtesyTitle,
@PhoneNumber,
@CFGName,
@UserLogin,
@UserName,
@Config,
@DTS,
@UsrID
)
SELECT @newUID= SCOPE_IDENTITY()
SELECT @newLastChanged=[LastChanged]
FROM [Users] WHERE [UID]=@newUID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addUser Succeeded'
ELSE PRINT 'Procedure Creation: addUser Error on Creation'
GO
/****** Object: StoredProcedure [deleteUser] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteUser];
GO
CREATE PROCEDURE [dbo].[deleteUser]
(
@UID int
)
AS
DELETE [Memberships]
WHERE [UID]=@UID
DELETE [Users]
WHERE [UID] = @UID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteUser Succeeded'
ELSE PRINT 'Procedure Creation: deleteUser Error on Creation'
GO
/****** Object: StoredProcedure [getUser] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getUser];
GO
CREATE PROCEDURE [dbo].[getUser]
(
@UID int
)
AS
SELECT
[UID],
[UserID],
[FirstName],
[MiddleName],
[LastName],
[Suffix],
[CourtesyTitle],
[PhoneNumber],
[CFGName],
[UserLogin],
[UserName],
[Config],
[DTS],
[UsrID],
[LastChanged],
(SELECT COUNT(*) FROM [Memberships] WHERE [Memberships].[UID]=[Users].[UID]) [MembershipCount]
FROM [Users]
WHERE [UID]=@UID
SELECT
[Memberships].[UGID],
[Memberships].[UID],
[Memberships].[GID],
[Memberships].[StartDate],
[Memberships].[EndDate],
[Memberships].[Config],
[Memberships].[DTS],
[Memberships].[UsrID],
[Memberships].[LastChanged],
[Groups].[GroupName] [Group_GroupName],
[Groups].[GroupType] [Group_GroupType],
[Groups].[Config] [Group_Config],
[Groups].[DTS] [Group_DTS],
[Groups].[UsrID] [Group_UsrID]
FROM [Memberships]
JOIN [Groups] ON
[Groups].[GID]=[Memberships].[GID]
WHERE
[Memberships].[UID]=@UID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getUser Succeeded'
ELSE PRINT 'Procedure Creation: getUser Error on Creation'
GO
/****** Object: StoredProcedure [existsUser] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsUser];
GO
CREATE PROCEDURE [dbo].[existsUser]
(
@UID int
)
AS
SELECT COUNT(*)
FROM [Users] WHERE [UID]=@UID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsUser Succeeded'
ELSE PRINT 'Procedure Creation: existsUser Error on Creation'
GO
/****** Object: StoredProcedure [getZContents] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getZContents]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getZContents];
GO
CREATE PROCEDURE [dbo].[getZContents]
AS
SELECT
[ContentID],
[OldStepSequence],
[LastChanged]
FROM [ZContents]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZContents Succeeded'
ELSE PRINT 'Procedure Creation: getZContents Error on Creation'
GO
/****** Object: StoredProcedure [updateZContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateZContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateZContent];
GO
CREATE PROCEDURE [dbo].[updateZContent]
(
@ContentID int,
@OldStepSequence nvarchar(32),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [ZContents]
SET
[OldStepSequence]=@OldStepSequence
WHERE [ContentID]=@ContentID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [ZContents] WHERE [ContentID]=@ContentID)
RAISERROR('ZContent record has been deleted by another user', 16, 1)
ELSE
RAISERROR('ZContent has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [ZContents] WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateZContent Succeeded'
ELSE PRINT 'Procedure Creation: updateZContent Error on Creation'
GO
/****** Object: StoredProcedure [addZContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addZContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addZContent];
GO
CREATE PROCEDURE [dbo].[addZContent]
(
@ContentID int,
@OldStepSequence nvarchar(32),
@newLastChanged timestamp output
)
AS
INSERT INTO [ZContents]
(
[ContentID],
[OldStepSequence]
)
VALUES
(
@ContentID,
@OldStepSequence
)
SELECT @newLastChanged=[LastChanged]
FROM [ZContents] WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addZContent Succeeded'
ELSE PRINT 'Procedure Creation: addZContent Error on Creation'
GO
/****** Object: StoredProcedure [deleteZContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteZContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteZContent];
GO
CREATE PROCEDURE [dbo].[deleteZContent]
(
@ContentID int
)
AS
DELETE [ZContents]
WHERE [ContentID] = @ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteZContent Succeeded'
ELSE PRINT 'Procedure Creation: deleteZContent Error on Creation'
GO
/****** Object: StoredProcedure [getZContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getZContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getZContent];
GO
CREATE PROCEDURE [dbo].[getZContent]
(
@ContentID int
)
AS
SELECT
[ContentID],
[OldStepSequence],
[LastChanged]
FROM [ZContents]
WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZContent Succeeded'
ELSE PRINT 'Procedure Creation: getZContent Error on Creation'
GO
/****** Object: StoredProcedure [existsZContent] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsZContent]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsZContent];
GO
CREATE PROCEDURE [dbo].[existsZContent]
(
@ContentID int
)
AS
SELECT COUNT(*)
FROM [ZContents] WHERE [ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsZContent Succeeded'
ELSE PRINT 'Procedure Creation: existsZContent Error on Creation'
GO
/****** Object: StoredProcedure [getZContentsByContentID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getZContentsByContentID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getZContentsByContentID];
GO
CREATE PROCEDURE [dbo].[getZContentsByContentID]
(
@ContentID int
)
AS
SELECT
[ZContents].[ContentID],
[ZContents].[OldStepSequence],
[ZContents].[LastChanged]
FROM [ZContents]
WHERE
[ZContents].[ContentID]=@ContentID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZContentsByContentID Succeeded'
ELSE PRINT 'Procedure Creation: getZContentsByContentID Error on Creation'
GO
/****** Object: StoredProcedure [getZTransitions] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getZTransitions]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getZTransitions];
GO
CREATE PROCEDURE [dbo].[getZTransitions]
AS
SELECT
[TransitionID],
[oldto],
[LastChanged]
FROM [ZTransitions]
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZTransitions Succeeded'
ELSE PRINT 'Procedure Creation: getZTransitions Error on Creation'
GO
/****** Object: StoredProcedure [updateZTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateZTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [updateZTransition];
GO
CREATE PROCEDURE [dbo].[updateZTransition]
(
@TransitionID int,
@Oldto nvarchar(32),
@LastChanged timestamp,
@newLastChanged timestamp output
)
AS
UPDATE [ZTransitions]
SET
[oldto]=@Oldto
WHERE [TransitionID]=@TransitionID AND [LastChanged]=@LastChanged
IF @@ROWCOUNT = 0
BEGIN
IF NOT exists(select * from [ZTransitions] WHERE [TransitionID]=@TransitionID)
RAISERROR('ZTransition record has been deleted by another user', 16, 1)
ELSE
RAISERROR('ZTransition has been edited by another user', 16, 1)
END
SELECT @newLastChanged=[LastChanged]
FROM [ZTransitions] WHERE [TransitionID]=@TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateZTransition Succeeded'
ELSE PRINT 'Procedure Creation: updateZTransition Error on Creation'
GO
/****** Object: StoredProcedure [addZTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[addZTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [addZTransition];
GO
CREATE PROCEDURE [dbo].[addZTransition]
(
@TransitionID int,
@Oldto nvarchar(32),
@newLastChanged timestamp output
)
AS
INSERT INTO [ZTransitions]
(
[TransitionID],
[oldto]
)
VALUES
(
@TransitionID,
@Oldto
)
SELECT @newLastChanged=[LastChanged]
FROM [ZTransitions] WHERE [TransitionID]=@TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: addZTransition Succeeded'
ELSE PRINT 'Procedure Creation: addZTransition Error on Creation'
GO
/****** Object: StoredProcedure [deleteZTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteZTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [deleteZTransition];
GO
CREATE PROCEDURE [dbo].[deleteZTransition]
(
@TransitionID int
)
AS
DELETE [ZTransitions]
WHERE [TransitionID] = @TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteZTransition Succeeded'
ELSE PRINT 'Procedure Creation: deleteZTransition Error on Creation'
GO
/****** Object: StoredProcedure [getZTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getZTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getZTransition];
GO
CREATE PROCEDURE [dbo].[getZTransition]
(
@TransitionID int
)
AS
SELECT
[TransitionID],
[oldto],
[LastChanged]
FROM [ZTransitions]
WHERE [TransitionID]=@TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZTransition Succeeded'
ELSE PRINT 'Procedure Creation: getZTransition Error on Creation'
GO
/****** Object: StoredProcedure [existsZTransition] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[existsZTransition]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [existsZTransition];
GO
CREATE PROCEDURE [dbo].[existsZTransition]
(
@TransitionID int
)
AS
SELECT COUNT(*)
FROM [ZTransitions] WHERE [TransitionID]=@TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: existsZTransition Succeeded'
ELSE PRINT 'Procedure Creation: existsZTransition Error on Creation'
GO
/****** Object: StoredProcedure [getZTransitionsByTransitionID] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getZTransitionsByTransitionID]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [getZTransitionsByTransitionID];
GO
CREATE PROCEDURE [dbo].[getZTransitionsByTransitionID]
(
@TransitionID int
)
AS
SELECT
[ZTransitions].[TransitionID],
[ZTransitions].[oldto],
[ZTransitions].[LastChanged]
FROM [ZTransitions]
WHERE
[ZTransitions].[TransitionID]=@TransitionID
RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getZTransitionsByTransitionID Succeeded'
ELSE PRINT 'Procedure Creation: getZTransitionsByTransitionID Error on Creation'
GO
Reference in New Issue View Git Blame Copy Permalink