/*

Navicat SQL Server Data Transfer

Source Server : db5-emswin-neotomaDev

Source Server Version : 110000

Source Host : db5.emswin.psu.edu:1433

Source Database : Neotoma

Source Schema : TS

Target Server Type : SQL Server

Target Server Version : 110000

File Encoding : 65001

Date: 2018-06-27 14:34:49

*/

-- ----------------------------

-- Procedure structure for CheckSteward

-- ----------------------------

CREATE PROCEDURE [CheckSteward]

@username nvarchar(80),

@pwd nvarchar(80)

AS

SET NOCOUNT ON

SELECT StewardID, authorized

FROM TS.StewardAuthorization

WHERE username = @username AND pwd = @pwd

RETURN

SET NOCOUNT OFF

GO

-- ----------------------------

-- Procedure structure for CombineContacts

-- ----------------------------

CREATE PROCEDURE [CombineContacts](@KEEPCONTACTID int, @CONTACTIDLIST nvarchar(MAX))

AS

-- DECLARE @KEEPCONTACTID int

-- SET @KEEPCONTACTID = 1524

-- DECLARE @CONTACTIDLIST nvarchar(MAX)

-- SET @CONTACTIDLIST = N'1524$7393$9040'

DECLARE @N int

DECLARE @CONTACTIDS TABLE (ID int NOT NULL primary key identity(1,1),

ContactID int)

INSERT INTO @CONTACTIDS (ContactID)

SELECT ContactID

FROM NDB.Contacts

WHERE (ContactID IN (

SELECT Value

FROM TI.func_NvarcharListToIN(@CONTACTIDLIST,'$')

))

DECLARE @NROWS int = @@ROWCOUNT

DECLARE @CURRENTID int = 0

DECLARE @CONTACTID int

WHILE @CURRENTID < @NROWS

BEGIN

SET @CURRENTID = @CURRENTID+1

SET @CONTACTID = (SELECT ContactID FROM @CONTACTIDS WHERE ID = @CURRENTID)

IF @CONTACTID <> @KEEPCONTACTID

BEGIN

IF (SELECT COUNT(ContactID) FROM NDB.PublicationAuthors WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.PublicationAuthors

SET NDB.PublicationAuthors.ContactID = @KEEPCONTACTID

WHERE (NDB.PublicationAuthors.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.Chronologies WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.Chronologies

SET NDB.Chronologies.ContactID = @KEEPCONTACTID

WHERE (NDB.Chronologies.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.Collectors WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.Collectors

SET NDB.Collectors.ContactID = @KEEPCONTACTID

WHERE (NDB.Collectors.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.ConstituentDatabases WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.ConstituentDatabases

SET NDB.ConstituentDatabases.ContactID = @KEEPCONTACTID

WHERE (NDB.ConstituentDatabases.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.DataProcessors WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.DataProcessors

SET NDB.DataProcessors.ContactID = @KEEPCONTACTID

WHERE (NDB.DataProcessors.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.DatasetPIs WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.DatasetPIs

SET NDB.DatasetPIs.ContactID = @KEEPCONTACTID

WHERE (NDB.DatasetPIs.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.DatasetSubmissions WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.DatasetSubmissions

SET NDB.DatasetSubmissions.ContactID = @KEEPCONTACTID

WHERE (NDB.DatasetSubmissions.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.DatasetTaxonNotes WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.DatasetTaxonNotes

SET NDB.DatasetTaxonNotes.ContactID = @KEEPCONTACTID

WHERE (NDB.DatasetTaxonNotes.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.DataTaxonNotes WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.DataTaxonNotes

SET NDB.DataTaxonNotes.ContactID = @KEEPCONTACTID

WHERE (NDB.DataTaxonNotes.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.SampleAnalysts WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.SampleAnalysts

SET NDB.SampleAnalysts.ContactID = @KEEPCONTACTID

WHERE (NDB.SampleAnalysts.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.SiteImages WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.SiteImages

SET NDB.SiteImages.ContactID = @KEEPCONTACTID

WHERE (NDB.SiteImages.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM TI.StewardUpdates WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE TI.StewardUpdates

SET TI.StewardUpdates.ContactID = @KEEPCONTACTID

WHERE (TI.StewardUpdates.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ContactID) FROM NDB.Synonymy WHERE (ContactID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.Synonymy

SET NDB.Synonymy.ContactID = @KEEPCONTACTID

WHERE (NDB.Synonymy.ContactID = @CONTACTID)

END

IF (SELECT COUNT(ValidatorID) FROM NDB.Taxa WHERE (ValidatorID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.Taxa

SET NDB.Taxa.ValidatorID = @KEEPCONTACTID

WHERE (NDB.Taxa.ValidatorID = @CONTACTID)

END

IF (SELECT COUNT(AnalystID) FROM NDB.IsoMetadata WHERE (AnalystID = @CONTACTID)) > 0

BEGIN

UPDATE NDB.IsoMetadata

SET NDB.IsoMetadata.AnalystID = @KEEPCONTACTID

WHERE (NDB.IsoMetadata.AnalystID = @CONTACTID)

END

DELETE FROM NDB.Contacts WHERE (NDB.Contacts.ContactID = @CONTACTID)

END

END

GO

-- ----------------------------

-- Procedure structure for DeleteAnalysisUnit

-- ----------------------------

CREATE PROCEDURE [DeleteAnalysisUnit](@ANALUNITID int)

AS

Delete from NDB.AnalysisUnits

WHERE AnalysisUnitID = @ANALUNITID

GO

-- ----------------------------

-- Procedure structure for DeleteChronology

-- ----------------------------

CREATE PROCEDURE [DeleteChronology](@CHRONOLOGYID int)

AS

/* Nuke GeochronControls */

DECLARE @GEOCHRONCONTROLS TABLE

(

ChronControlID int,

GeochronID int,

PRIMARY KEY (ChronControlID, GeochronID)

)

INSERT INTO @GEOCHRONCONTROLS(ChronControlID, GeochronID)

SELECT NDB.GeoChronControls.ChronControlID, NDB.GeoChronControls.GeochronID

FROM NDB.Chronologies INNER JOIN

NDB.ChronControls ON NDB.Chronologies.ChronologyID = NDB.ChronControls.ChronologyID INNER JOIN

NDB.GeoChronControls ON NDB.ChronControls.ChronControlID = NDB.GeoChronControls.ChronControlID

WHERE (NDB.Chronologies.ChronologyID = @CHRONOLOGYID)

DECLARE @CHRONCONTROLID int

DECLARE @GEOCHRONID int

WHILE (SELECT COUNT(*) From @GEOCHRONCONTROLS) > 0

BEGIN

SET @CHRONCONTROLID = (SELECT TOP (1) ChronControlID FROM @GEOCHRONCONTROLS)

SET @GEOCHRONID = (SELECT TOP (1) GeochronID FROM @GEOCHRONCONTROLS)

DELETE FROM NDB.GeoChronControls WHERE (ChronControlID = @CHRONCONTROLID AND GeochronID = @GEOCHRONID)

DELETE FROM @GEOCHRONCONTROLS WHERE (ChronControlID = @CHRONCONTROLID AND GeochronID = @GEOCHRONID)

END

/* Nuke ChronologY */

DELETE FROM NDB.Chronologies WHERE ChronologyID = @CHRONOLOGYID

GO

-- ----------------------------

-- Procedure structure for DeleteCollectionUnit

-- ----------------------------

CREATE PROCEDURE [DeleteCollectionUnit](@COLLECTIONUNITID int)

AS

/* Nuke GeochronControls */

DECLARE @GEOCHRONCONTROLS TABLE

(

ChronControlID int,

GeochronID int,

PRIMARY KEY (ChronControlID, GeochronID)

)

INSERT INTO @GEOCHRONCONTROLS(ChronControlID, GeochronID)

SELECT NDB.GeoChronControls.ChronControlID, NDB.GeoChronControls.GeochronID

FROM NDB.CollectionUnits INNER JOIN

NDB.Chronologies ON NDB.CollectionUnits.CollectionUnitID = NDB.Chronologies.CollectionUnitID INNER JOIN

NDB.ChronControls ON NDB.Chronologies.ChronologyID = NDB.ChronControls.ChronologyID INNER JOIN

NDB.GeoChronControls ON NDB.ChronControls.ChronControlID = NDB.GeoChronControls.ChronControlID

WHERE (NDB.CollectionUnits.CollectionUnitID = @COLLECTIONUNITID)

DECLARE @CHRONCONTROLID int

DECLARE @GEOCHRONID int

WHILE (SELECT COUNT(*) From @GEOCHRONCONTROLS) > 0

BEGIN

SET @CHRONCONTROLID = (SELECT TOP (1) ChronControlID FROM @GEOCHRONCONTROLS)

SET @GEOCHRONID = (SELECT TOP (1) GeochronID FROM @GEOCHRONCONTROLS)

DELETE FROM NDB.GeoChronControls WHERE (ChronControlID = @CHRONCONTROLID AND GeochronID = @GEOCHRONID)

DELETE FROM @GEOCHRONCONTROLS WHERE (ChronControlID = @CHRONCONTROLID AND GeochronID = @GEOCHRONID)

END

/* Nuke Chronologies */

DECLARE @CHRONOLOGYIDS TABLE

(

ChronologyID int PRIMARY KEY

)

INSERT INTO @CHRONOLOGYIDS(ChronologyID)

SELECT NDB.Chronologies.ChronologyID

FROM NDB.CollectionUnits INNER JOIN

NDB.Chronologies ON NDB.CollectionUnits.CollectionUnitID = NDB.Chronologies.CollectionUnitID

WHERE (NDB.CollectionUnits.CollectionUnitID = @COLLECTIONUNITID)

DECLARE @CHRONID int

WHILE (SELECT COUNT(*) From @CHRONOLOGYIDS) > 0

BEGIN

SET @CHRONID = (SELECT TOP 1 ChronologyID FROM @CHRONOLOGYIDS)

DELETE FROM NDB.Chronologies WHERE ChronologyID = @CHRONID

DELETE FROM @CHRONOLOGYIDS WHERE ChronologyID = @CHRONID

END

/* Nuke Samples */

DECLARE @SAMPLEIDS TABLE

(

SampleID int PRIMARY KEY

)

INSERT INTO @SAMPLEIDS(SampleID)

SELECT NDB.Samples.SampleID

FROM NDB.CollectionUnits INNER JOIN

NDB.AnalysisUnits ON NDB.CollectionUnits.CollectionUnitID = NDB.AnalysisUnits.CollectionUnitID INNER JOIN

NDB.Samples ON NDB.AnalysisUnits.AnalysisUnitID = NDB.Samples.AnalysisUnitID

WHERE (NDB.CollectionUnits.CollectionUnitID = @COLLECTIONUNITID)

DECLARE @SAMPLEID int

WHILE (SELECT COUNT(*) From @SAMPLEIDS) > 0

BEGIN

SET @SAMPLEID = (SELECT TOP 1 SampleID FROM @SAMPLEIDS)

DELETE FROM NDB.Samples WHERE SampleID = @SAMPLEID

DELETE FROM @SAMPLEIDS WHERE SampleID = @SAMPLEID

END

/* Nuke AnalysisUnits */

DECLARE @ANALUNITIDS TABLE

(

AnalUnitID int PRIMARY KEY

)

INSERT INTO @ANALUNITIDS(AnalUnitID)

SELECT NDB.AnalysisUnits.AnalysisUnitID

FROM NDB.CollectionUnits INNER JOIN

NDB.AnalysisUnits ON NDB.CollectionUnits.CollectionUnitID = NDB.AnalysisUnits.CollectionUnitID

WHERE (NDB.CollectionUnits.CollectionUnitID = @COLLECTIONUNITID)

DECLARE @ANALUNITID int

WHILE (SELECT COUNT(*) From @ANALUNITIDS) > 0

BEGIN

SET @ANALUNITID = (SELECT TOP 1 AnalUnitID FROM @ANALUNITIDS)

DELETE FROM NDB.AnalysisUnits WHERE AnalysisUnitID = @ANALUNITID

DELETE FROM @ANALUNITIDS WHERE AnalUnitID = @ANALUNITID

END

DELETE FROM NDB.CollectionUnits

WHERE CollectionUnitID = @COLLECTIONUNITID

GO

-- ----------------------------

-- Procedure structure for DeleteCollectors

-- ----------------------------

CREATE PROCEDURE [DeleteCollectors](@COLLUNITID int)

AS

Delete from NDB.Collectors

WHERE CollectionUnitID = @COLLUNITID

GO

-- ----------------------------

-- Procedure structure for DeleteData

-- ----------------------------

CREATE PROCEDURE [DeleteData](@DATAID int)

AS

Delete from NDB.Data

WHERE DataID = @DATAID

GO

-- ----------------------------

-- Procedure structure for DeleteDataProcessor

-- ----------------------------

CREATE PROCEDURE [DeleteDataProcessor](@DATASETID int, @CONTACTID int)

AS

DELETE FROM NDB.DataProcessors

WHERE (NDB.DataProcessors.DatasetID = @DATASETID) AND (NDB.DataProcessors.ContactID = @CONTACTID)

GO

-- ----------------------------

-- Procedure structure for DeleteDataset

-- ----------------------------

CREATE PROCEDURE [DeleteDataset](@DATASETID int)

AS

DELETE FROM NDB.Samples

WHERE DatasetID = @DATASETID

DELETE FROM NDB.Datasets

WHERE DatasetID = @DATASETID

GO

-- ----------------------------

-- Procedure structure for DeleteDatasetPI

-- ----------------------------

CREATE PROCEDURE [DeleteDatasetPI](@DATASETID int, @CONTACTID int)

AS

DECLARE @DATASETPIS TABLE

(

ID int NOT NULL primary key identity(1,1),

ContactID int,

PIOrder int

)

DELETE FROM NDB.DatasetPIs

WHERE (NDB.DatasetPIs.DatasetID = @DATASETID) AND (NDB.DatasetPIs.ContactID = @CONTACTID)

DECLARE @NPIS int

SET @NPIS = (SELECT COUNT(*) FROM NDB.DatasetPIs WHERE (DatasetID = @DATASETID))

IF @NPIS > 0

BEGIN

INSERT INTO @DATASETPIS (ContactID, PIOrder)

SELECT ContactID, PIOrder FROM NDB.DatasetPIs WHERE (DatasetID = @DATASETID)

ORDER BY NDB.DatasetPIs.PIOrder

DECLARE @TContactID int

DECLARE @CurrentID int = 0

WHILE @CurrentID < @NPIS

BEGIN

SET @CurrentID = @CurrentID+1

SET @TContactID = (SELECT ContactID FROM @DATASETPIS WHERE (ID = @CurrentID))

UPDATE NDB.DatasetPIs

SET NDB.DatasetPIs.PIOrder = @CurrentID

WHERE (NDB.DatasetPIs.DatasetID = @DATASETID) AND (NDB.DatasetPIs.ContactID = @TContactID)

END

END

GO

-- ----------------------------

-- Procedure structure for DeleteDatasetPublication

-- ----------------------------

CREATE PROCEDURE [DeleteDatasetPublication](@DATASETID int, @PUBLICATIONID int)

AS

DELETE FROM NDB.DatasetPublications

WHERE (DatasetID = @DATASETID AND PublicationID = @PUBLICATIONID)

GO

-- ----------------------------

-- Procedure structure for DeleteDatasetTaxonNotes

-- ----------------------------

CREATE PROCEDURE [DeleteDatasetTaxonNotes](@DATASETID int, @TAXONID int, @CONTACTID int)

AS

Delete from NDB.DatasetTaxonNotes

WHERE DatasetID = @DATASETID AND TaxonID = @TAXONID

INSERT INTO TI.StewardUpdates(ContactID, TableName, PK1, PK2, Operation)

VALUES (@CONTACTID, N'DatasetTaxonNotes', @DATASETID, @TAXONID, N'Delete')

GO

-- ----------------------------

-- Procedure structure for DeleteDatasetVariable

-- ----------------------------

CREATE PROCEDURE [DeleteDatasetVariable](@DATASETID int, @VARIABLEID int)

AS

DELETE FROM NDB.Data

WHERE DataID in (SELECT NDB.Data.DataID

FROM NDB.Samples INNER JOIN

NDB.Data ON NDB.Samples.SampleID = NDB.Data.SampleID

WHERE (NDB.Samples.DatasetID = @DATASETID) AND (NDB.Data.VariableID = @VARIABLEID))

GO

-- ----------------------------

-- Procedure structure for DeleteDepEnvtType

-- ----------------------------

CREATE PROCEDURE [DeleteDepEnvtType](@DEPENVTID int)

AS Delete from NDB.DepEnvtTypes

WHERE DEPENVTID = @DEPENVTID

GO

-- ----------------------------

-- Procedure structure for DeleteEcolGroup

-- ----------------------------

CREATE PROCEDURE [DeleteEcolGroup](@TAXONID int, @ECOLSETID int)

AS

Delete from NDB.EcolGroups

WHERE TaxonID = @TAXONID and EcolSetID = @ECOLSETID

GO

-- ----------------------------

-- Procedure structure for DeleteEventPublication

-- ----------------------------

CREATE PROCEDURE [DeleteEventPublication](@EVENTID int, @PUBLICATIONID int)

AS

Delete from NDB.EventPublications

WHERE EventID = @EVENTID and PublicationID = @PUBLICATIONID

GO

-- ----------------------------

-- Procedure structure for DeleteGeochron

-- ----------------------------

CREATE PROCEDURE [DeleteGeochron](@GEOCHRONID int)

AS

IF (SELECT COUNT(GeochronID) FROM NDB.GeoChronControls WHERE (GeochronID = @GEOCHRONID)) > 0

BEGIN

DELETE FROM NDB.GeoChronControls

WHERE (GeochronID = @GEOCHRONID)

END

DELETE FROM NDB.Geochronology

WHERE GeochronID = @GEOCHRONID

GO

-- ----------------------------

-- Procedure structure for DeleteGeochronControl

-- ----------------------------

CREATE PROCEDURE [DeleteGeochronControl](@CHRONCONTROLID int)

AS

IF (SELECT COUNT(ChronControlID) FROM NDB.GeoChronControls WHERE (ChronControlID = @CHRONCONTROLID)) > 0

BEGIN

DELETE FROM NDB.GeoChronControls

WHERE (ChronControlID = @CHRONCONTROLID)

END

DELETE FROM NDB.ChronControls

WHERE (ChronControlID = @CHRONCONTROLID)

GO

-- ----------------------------

-- Procedure structure for DeleteGeochronPublication

-- ----------------------------

CREATE PROCEDURE [DeleteGeochronPublication](@GEOCHRONID int, @PUBLICATIONID int)

AS

DELETE FROM NDB.GeochronPublications

WHERE (GeochronID = @GEOCHRONID AND PublicationID = @PUBLICATIONID)

GO

-- ----------------------------

-- Procedure structure for DeletePublicationAuthor

-- ----------------------------

CREATE PROCEDURE [DeletePublicationAuthor](@AUTHORID int)

AS Delete from NDB.PublicationAuthors

WHERE AuthorID = @AUTHORID

GO

-- ----------------------------

-- Procedure structure for DeletePublicationEditor

-- ----------------------------

CREATE PROCEDURE [DeletePublicationEditor](@EDITORID int)

AS Delete from NDB.PublicationEditors

WHERE EditorID = @EDITORID

GO

-- ----------------------------

-- Procedure structure for DeletePublicationTranslator

-- ----------------------------

CREATE PROCEDURE [DeletePublicationTranslator](@TRANSLATORID int)

AS Delete from NDB.PublicationTranslators

WHERE TranslatorID = @TRANSLATORID

GO

-- ----------------------------

-- Procedure structure for DeleteRelativeAgePublication

-- ----------------------------

CREATE PROCEDURE [DeleteRelativeAgePublication](@RELATIVEAGEID int, @PUBLICATIONID int)

AS

Delete from NDB.RelativeAgePublications

WHERE RelativeAgeID = @RELATIVEAGEID and PublicationID = @PUBLICATIONID

GO

-- ----------------------------

-- Procedure structure for DeleteRepositorySpecimen

-- ----------------------------

CREATE PROCEDURE [DeleteRepositorySpecimen](@DATASETID int, @REPOSITORYID int)

AS

DELETE FROM NDB.RepositorySpecimens

WHERE (DatasetID = @DATASETID) AND (RepositoryID = @REPOSITORYID)

-- INSERT INTO TI.StewardUpdates(ContactID, TableName, PK1, PK2, Operation)

-- VALUES (@CONTACTID, N'RepositorySpecimens', @DATASETID, @REPOSITORYID, N'Delete')

GO

-- ----------------------------

-- Procedure structure for DeleteSample

-- ----------------------------

CREATE PROCEDURE [DeleteSample](@SAMPLEID int)

AS

Delete from NDB.Samples

WHERE SampleID = @SAMPLEID

GO

-- ----------------------------

-- Procedure structure for DeleteSampleAnalysts

-- ----------------------------

CREATE PROCEDURE [DeleteSampleAnalysts](@SAMPLEID int)

AS

DELETE FROM NDB.SampleAnalysts

WHERE SampleID = @SAMPLEID

GO

-- ----------------------------

-- Procedure structure for DeleteSite

-- ----------------------------

CREATE PROCEDURE [DeleteSite](@SITEID int)

AS

/* Nuke GeochronControls */

DECLARE @GEOCHRONCONTROLS TABLE

(

ChronControlID int,

GeochronID int,

PRIMARY KEY (ChronControlID, GeochronID)

)

INSERT INTO @GEOCHRONCONTROLS(ChronControlID, GeochronID)

SELECT NDB.GeoChronControls.ChronControlID, NDB.GeoChronControls.GeochronID

FROM NDB.Sites INNER JOIN

NDB.CollectionUnits ON NDB.Sites.SiteID = NDB.CollectionUnits.SiteID INNER JOIN

NDB.Chronologies ON NDB.CollectionUnits.CollectionUnitID = NDB.Chronologies.CollectionUnitID INNER JOIN

NDB.ChronControls ON NDB.Chronologies.ChronologyID = NDB.ChronControls.ChronologyID INNER JOIN

NDB.GeoChronControls ON NDB.ChronControls.ChronControlID = NDB.GeoChronControls.ChronControlID

WHERE (NDB.Sites.SiteID = @SITEID)

DECLARE @CHRONCONTROLID int

DECLARE @GEOCHRONID int

WHILE (SELECT COUNT(*) From @GEOCHRONCONTROLS) > 0

BEGIN

SET @CHRONCONTROLID = (SELECT TOP (1) ChronControlID FROM @GEOCHRONCONTROLS)

SET @GEOCHRONID = (SELECT TOP (1) GeochronID FROM @GEOCHRONCONTROLS)

DELETE FROM NDB.GeoChronControls WHERE (ChronControlID = @CHRONCONTROLID AND GeochronID = @GEOCHRONID)

DELETE FROM @GEOCHRONCONTROLS WHERE (ChronControlID = @CHRONCONTROLID AND GeochronID = @GEOCHRONID)

END

/* Nuke Chronologies */

DECLARE @CHRONOLOGYIDS TABLE

(

ChronologyID int PRIMARY KEY

)

INSERT INTO @CHRONOLOGYIDS(ChronologyID)

SELECT NDB.Chronologies.ChronologyID

FROM NDB.Sites INNER JOIN

NDB.CollectionUnits ON NDB.Sites.SiteID = NDB.CollectionUnits.SiteID INNER JOIN

NDB.Chronologies ON NDB.CollectionUnits.CollectionUnitID = NDB.Chronologies.CollectionUnitID

WHERE (NDB.Sites.SiteID = @SITEID)

DECLARE @CHRONID int

WHILE (SELECT COUNT(*) From @CHRONOLOGYIDS) > 0

BEGIN

SET @CHRONID = (SELECT TOP 1 ChronologyID FROM @CHRONOLOGYIDS)

DELETE FROM NDB.Chronologies WHERE ChronologyID = @CHRONID

DELETE FROM @CHRONOLOGYIDS WHERE ChronologyID = @CHRONID

END

/* Nuke Samples */

DECLARE @SAMPLEIDS TABLE

(

SampleID int PRIMARY KEY

)

INSERT INTO @SAMPLEIDS(SampleID)

SELECT NDB.Samples.SampleID

FROM NDB.Sites INNER JOIN

NDB.CollectionUnits ON NDB.Sites.SiteID = NDB.CollectionUnits.SiteID INNER JOIN

NDB.AnalysisUnits ON NDB.CollectionUnits.CollectionUnitID = NDB.AnalysisUnits.CollectionUnitID INNER JOIN

NDB.Samples ON NDB.AnalysisUnits.AnalysisUnitID = NDB.Samples.AnalysisUnitID

WHERE (NDB.Sites.SiteID = @SITEID)

DECLARE @SAMPLEID int

WHILE (SELECT COUNT(*) From @SAMPLEIDS) > 0

BEGIN

SET @SAMPLEID = (SELECT TOP 1 SampleID FROM @SAMPLEIDS)

DELETE FROM NDB.Samples WHERE SampleID = @SAMPLEID

DELETE FROM @SAMPLEIDS WHERE SampleID = @SAMPLEID

END

/* Nuke AnalysisUnits */

DECLARE @ANALUNITIDS TABLE

(

AnalUnitID int PRIMARY KEY

)

INSERT INTO @ANALUNITIDS(AnalUnitID)

SELECT NDB.AnalysisUnits.AnalysisUnitID

FROM NDB.Sites INNER JOIN

NDB.CollectionUnits ON NDB.Sites.SiteID = NDB.CollectionUnits.SiteID INNER JOIN

NDB.AnalysisUnits ON NDB.CollectionUnits.CollectionUnitID = NDB.AnalysisUnits.CollectionUnitID

WHERE (NDB.Sites.SiteID = @SITEID)

DECLARE @ANALUNITID int

WHILE (SELECT COUNT(*) From @ANALUNITIDS) > 0

BEGIN

SET @ANALUNITID = (SELECT TOP 1 AnalUnitID FROM @ANALUNITIDS)

DELETE FROM NDB.AnalysisUnits WHERE AnalysisUnitID = @ANALUNITID

DELETE FROM @ANALUNITIDS WHERE AnalUnitID = @ANALUNITID

END

DELETE FROM NDB.Sites

WHERE SiteID = @SITEID

GO

-- ----------------------------

-- Procedure structure for DeleteSynonymy

-- ----------------------------

CREATE PROCEDURE [DeleteSynonymy](@SYNONYMYID int, @CONTACTID int)

AS

Delete from NDB.Synonymy

WHERE SynonymyID = @SYNONYMYID

INSERT INTO TI.StewardUpdates(ContactID, TableName, PK1, Operation)

VALUES (@CONTACTID, N'Synonymy', @SYNONYMYID, N'Delete')

GO

-- ----------------------------

-- Procedure structure for DeleteTaxon

-- ----------------------------

CREATE PROCEDURE [DeleteTaxon](@TAXONID int)

AS

IF (SELECT COUNT(HigherTaxonID) FROM NDB.TaxaAltHierarchy WHERE (HigherTaxonID = @TAXONID)) > 0

BEGIN

DELETE FROM NDB.TaxaAltHierarchy

WHERE (HigherTaxonID = @TAXONID)

END

DELETE FROM NDB.Taxa

WHERE TaxonID = @TAXONID

GO

-- ----------------------------

-- Procedure structure for DeleteVariableByVariableID

-- ----------------------------

CREATE PROCEDURE [DeleteVariableByVariableID](@VARIABLEID int)

AS

Delete from NDB.Variables

WHERE VariableID = @VARIABLEID

GO

-- ----------------------------

-- Procedure structure for DeleteVariableContext

-- ----------------------------

CREATE PROCEDURE [DeleteVariableContext](@VARIABLECONTEXTID int)

AS

Delete from NDB.VariableContexts

WHERE VariableContextID = @VARIABLECONTEXTID

GO

-- ----------------------------

-- Procedure structure for DeleteVariablesByTaxonID

-- ----------------------------

CREATE PROCEDURE [DeleteVariablesByTaxonID](@TAXONID int)

AS

Delete from NDB.Variables

WHERE TaxonID = @TAXONID

GO

-- ----------------------------

-- Procedure structure for InsertAccumulationRate

-- ----------------------------

CREATE PROCEDURE [InsertAccumulationRate](@ANALYSISUNITID int, @CHRONOLOGYID int, @ACCRATE float, @VARIABLEUNITSID int)

AS INSERT INTO NDB.AccumulationRates(AnalysisUnitID, ChronologyID, AccumulationRate, VariableUnitsID)

VALUES (@ANALYSISUNITID, @CHRONOLOGYID, @ACCRATE, @VARIABLEUNITSID)

GO