Update Values with Staff Codes

The Cst_Si_Configurations_Update_Values_GetStudLifeCycle_VendorCheckBypassUsers.sql script updates the Values column in the Si_Configurations table with comma-separated Staff Codes.

For more details about the script, refer to Si_Configurations Table Update Scripts.

declare @RowCount int
declare @UserID int
declare @data varchar(max)
 
set @RowCount = 0
--Getting the value as comma separated list <Start>
/*
select @data = COALESCE(@data + ',' + convert(varchar,ltrim(rtrim(Code))), convert(varchar,ltrim(rtrim(Code))))
from dbo.systaff(nolock) where active = 1 order by code
*/
--Getting the value as comma seperated list <End>
--enter the value as comma seperated list
set @data = ''
 
begin try
begin transaction
set @UserID = dbo.fn_SystemStaffID()
 
if exists (select 'true' from dbo.si_Configurations(nolock) where ltrim(rtrim([Key])) ='GetStudLifeCycle_VendorCheckBypassUsers')
begin
update dbo.si_Configurations
set Value = @data,
UserId = @UserID,
DateLstMod = getDate()
from dbo.si_Configurations(nolock)
where ltrim(rtrim([Key])) ='GetStudLifeCycle_VendorCheckBypassUsers'
select @RowCount = @RowCount + @@rowcount
 
exec dbo.sproc_System_Script_AffectedRecords_Update @RowCount
print 'DML: Cst_Si_Configurations_Update_Values_GetStudLifeCycle_VendorCheckBypassUsers.sql ran and Update '+ cast(@RowCount as varchar(10)) + ' row GetStudLifeCycle_VendorCheckBypassUsers in Si_Configurations table.'
end
commit transaction
end try
begin catch
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
declare @ErrorState int;
if (@@trancount !=0) rollback transaction
 
SELECT @ErrorMessage = error_message(),
@ErrorSeverity = error_severity(),
@ErrorState = error_State();
Raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch
go