

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/AppStudioDB/Sp/QS_UpdateWFExtendedPropertiesTable.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
--
-- ----------------------------------------------------------------------*/
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='QS_UpdateWFExtendedPropertiesTable')
BEGIN
	print '>>> Drop Stored Procedure: QS_UpdateWFExtendedPropertiesTable <<<'
	drop procedure QS_UpdateWFExtendedPropertiesTable
END
IF EXISTS (select * from GxQscripts where name='QS_UpdateWFExtendedPropertiesTable')
	delete from GxQscripts where name = 'QS_UpdateWFExtendedPropertiesTable'
GO

IF EXISTS (select * from GXDBVersions where aliasname='QS_UpdateWFExtendedPropertiesTable')
	delete from GXDBVersions where aliasname = 'QS_UpdateWFExtendedPropertiesTable'
GO
print '... Creating Procedure: QS_UpdateWFExtendedPropertiesTable'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure QS_UpdateWFExtendedPropertiesTable
-- Qscript is Enabled
-- Set Audit Level: NO_AUDIT_TRAIL, AUDIT_TRAIL_LEVEL_LOW, _MEDIUM, _HIGH, _CRITICAL
-- Qscript Usage. Do not use special char in HTML
-- SP Input Params
  @CustomProp nvarchar(max)=NULL
AS
Set NOCOUNT ON
------------ Convenience Definitions -----------
    DECLARE @scriptName varchar(64)
    SET     @scriptName = 'QS_UpdateWFExtendedPropertiesTable'
	DECLARE @lastError  integer
    SET     @lastError  = 0
    DECLARE @localErrorMsg      varchar(512)
    SET     @localErrorMsg          =''
	IF OBJECT_ID('tempdb.dbo.#temp_cprop') is not null
			DROP TABLE #temp_cprop
------------ Get Valid/Invalid Custom Properties from Input -----------
	DECLARE @i_table nvarchar(32) = N'WF_Extended_Properties'
	DECLARE @newcprop nvarchar(max)
	DECLARE @invalid_props nvarchar(max)
	DECLARE @operation nvarchar(512) = 'Not Updated Table ' + @i_table
	BEGIN TRAN
	IF @CustomProp IS NOT NULL and @CustomProp <> ''
	BEGIN
		Select @newcprop =  COALESCE(@newcprop+',' ,'') + Item from	(SELECT DISTINCT(ITEM) from dbo.AZ_StringSplitFunction (@CustomProp,',') WHERE ITEM LIKE 'CustomProp[_]%' AND COALESCE (ITEM, '') <> '' AND ITEM NOT IN (SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @i_table)) temp
		DECLARE @validprops nvarchar(max) = @newcprop
		IF @validprops <> ''
		BEGIN
			SELECT @invalid_props = COALESCE(@invalid_props+',' ,'') + Item from dbo.AZ_StringSplitFunction(@CustomProp,',') WHERE ITEM NOT IN (Select Item from dbo.AZ_StringSplitFunction(@newcprop,','))
		END
		ELSE
			SET @invalid_props = @CustomProp
		IF @newcprop IS NOT NULL and @newcprop <> ''
		BEGIN
			select (Item) into #temp_cprop from AZ_StringSplitFunction(@newcprop,',')
			UPDATE #temp_cprop SET ITEM = CONCAT(QUOTENAME(Item),' NVARCHAR(MAX) NULL')
			DECLARE @cprop_placeholder nvarchar(max)
			Select @cprop_placeholder = COALESCE(@cprop_placeholder+',','') + Item from #temp_cprop
			SET @newcprop = @cprop_placeholder
------------ Update Table ------------
			BEGIN TRY
				DECLARE @update_query nvarchar(max)= N'ALTER table '+CONCAT('dbo.',QUOTENAME(@i_table))+' ADD '+@newcprop+''
				EXEC sp_executesql @update_query
				IF (@@ERROR <> 0)
				BEGIN
					SET @lastError = @@ERROR
					SET @localErrorMsg = 'Failed to add custom props to the table'
					GOTO ERROR_EXIT
				END
				SET @operation = 'Updated Existing Table ' + @i_table
			END TRY
			BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
				SET @lastError = ERROR_NUMBER()
				SET @localErrorMsg = (SELECT ERROR_MESSAGE())
			END CATCH
		END
	END
ERROR_EXIT:
	IF @lastError <> 0
		ROLLBACK TRAN
	ELSE
		COMMIT TRAN
PROC_ERROR:
    PRINT ''
    PRINT @scriptName + ' completed at '
                    + Convert(varchar(60), getdate())+' with parameters'
					+ ' Operation ['+ @operation + '].'
                    + ' Used CustomProp  ['+  ISNULL(@validprops,'') + '].'
					+ ' Invalid Custom Props (' + ISNULL(@invalid_props,'') +').'
					+ ' ErrorCode (' + Convert(varchar(2),@lastError) +').'
					+ ' Error Message (' + @localErrorMsg +').'
	if @lastError <> 0
    Raiserror(@localErrorMsg,
                15,  --Severity (> 10 to force DOS Error Code = 1)
                1    --State
                )
GO

IF EXISTS (select * from GxQscripts where name = 'QS_UpdateWFExtendedPropertiesTable')
	delete from GxQscripts where name = 'QS_UpdateWFExtendedPropertiesTable'
GO

IF EXISTS (select * from GXDBVersions where aliasname='QS_UpdateWFExtendedPropertiesTable')
	delete from GXDBVersions where aliasname = 'QS_UpdateWFExtendedPropertiesTable'
GO

insert into GXDBVersions values(2, 'QS_UpdateWFExtendedPropertiesTable',  '00000000000000000000', 'QS_UpdateWFExtendedPropertiesTable', '00000000000000000000')
GO

insert into GxQscripts values(2,'QS_UpdateWFExtendedPropertiesTable',  4, GETDATE(), GETDATE(), '' + CHAR(10) + '	qoperation execscript -sn QS_UpdateWFExtendedPropertiesTable -si CustomProp
'
 + CHAR(10) + '	Example: qoperation execscript -sn QS_UpdateWFExtendedPropertiesTable -si CustomProp=''CustomProp_App,CustomProp_BU'' 
'
 + CHAR(10) + '-- 	script_name: QS_UpdateWFExtendedPropertiesTable.'
 + CHAR(10) + '-- 	CustomProp - Custom Properties .For Example ''CustomProp_App,CustomProp_BU'''
)
GO

