sproc for updateMapUse

updateMapUse

-- =============================================
-- Author:    	Michael Cox
-- Create date: 03/25/2010
-- Description:	Update map use information for a park
-- =============================================
CREATE PROCEDURE [mapProduction].[updateMapUseInfo] 
	@unitCode varchar(10) = NULL,
	@mapsList XML = NULL,
	@retCode int = 1 OUTPUT,
	@retMsg varchar(200) = 'OK' OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	
	BEGIN TRY
        BEGIN TRAN
		-- CREATE temp table
			CREATE TABLE dbo.#mapsXref
			(
				npsUnitCode varchar(5) COLLATE database_default NOT NULL,
				gmapIdF int NOT NULL,
				useForThisPark varchar(10) collate database_default NOT NULL, 
				citation varchar(max)
			)
			
		-- parse the xml out into the temp table
			INSERT INTO dbo.#mapsXref (npsUnitCode, gmapIdF, useForThisPark, citation)
			SELECT @unitCode As npsUnitCode, 
				T.item.value('gmapId[1]','INT') As gmapIdF,
				T.item.value('use[1]','VARCHAR(10)') As useForThisPark ,
				T.item.value('citation[1]', 'VARCHAR(MAX)') As citation
				FROM @mapsList.nodes('maps/map') AS T(item)
			
		   INSERT INTO dbo.mapsXrefAudit(npsUnitCode, gmapIdF, useForThisPark, citation)
		   SELECT npsUnitCode, gmapIdF, useForThisPark, citation from dbo.#mapsXref
			
		
		UPDATE map.XREF_GMAP_ID_TRANSLATIONS 
			SET useForThisPark = t.useForThisPark
			FROM map.XREF_GMAP_ID_TRANSLATIONS x INNER JOIN dbo.#mapsXref t 
				ON x.gmapIdF = t.gmapIdF WHERE x.npsUnitCode = t.npsUnitCode
			
		UPDATE map.TBL_GMAPS
			SET reference = t.citation
			FROM  dbo.#mapsXref t INNER JOIN map.TBL_GMAPS g
				ON g.gmapId = t.gmapIdF
				            
        COMMIT
        SELECT @retMsg = 'Map Use Info Updated', @retCode=1
	END TRY

	BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        EXEC handleError
        RETURN -1           
	END CATCH
	
END

GO