SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[adminAddAllEnvironments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[adminAddAllEnvironments]
GO


create procedure dbo.adminAddAllEnvironments
as
/*************************************************************************
Create the specified project SQL Server metadatabase.
Note that this procedure does not create the project and environment 
sub-projects in the specified SourceSafe Share. Only the SourceSafe Admin 
should have ability to create these SourceSafe Projects. All other users 
should have Read Only permissions to the project and to all Environment
projects other than the environment project to which they belong. In that
environment project, the SourceSafe Admin will grant "Add/Rename/Delete"
permissions before this Change Control subsystem can be used by that user.  
**************************************************************************/
set nocount on

declare @err int,
	@procedureName varchar(100)

set @procedureName = db_name() + '.' 
		+ user_name(objectproperty(@@procid,'OwnerId')) 
		+ '.' + object_name(@@procid)

-- populate environment
-- top (first in process) level has no parent (NULL)
insert Admin.dbo.Environment 
        (environmentName, 
        environmentDescription)
select  'Unit', 
        'Database Development/Unit Testing'
where not exists (select 1
			from Admin.dbo.Environment 
			where environmentName = 'Unit')
if @err <> 0
	goto ErrorHandler

insert Admin.dbo.Environment 
        (environmentName, 
        environmentDescription, 
        parentEnvironment_id)
select  'System', 
        'Application Development/System Test',
        id
from Admin.dbo.Environment
where environmentName = 'Unit' 
and not exists (select 1
			from Admin.dbo.Environment 
			where environmentName = 'System')
if @err <> 0
	goto ErrorHandler

insert Admin.dbo.Environment 
        (environmentName, 
        environmentDescription, 
        parentEnvironment_id)
select  'QA', 
        'Quality Control',
        id
from Admin.dbo.Environment
where environmentName = 'System' 
and not exists (select 1
			from Admin.dbo.Environment 
			where environmentName = 'QA')
if @err <> 0
	goto ErrorHandler

insert Admin.dbo.Environment 
        (environmentName, 
        environmentDescription, 
        parentEnvironment_id)
select 'Production', 
       'Production',
        id
from Admin.dbo.Environment
where environmentName = 'QA' 
and not exists (select 1
			from Admin.dbo.Environment 
			where environmentName = 'Production')
if @err <> 0
	goto ErrorHandler

-- audit
insert Admin.dbo.ActivityLog (activity)
select @procedureName + ' complete successfully'

return

ErrorHandler:
insert Admin.dbo.ActivityLog (activity)
select @procedureName + ' failed with err: ' + cast(@err as varchar(11)) 
raiserror(59001,16,1,@procedureName)

return -1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO