Last active
May 23, 2016 02:48
-
-
Save smarenich/5702a4adcdaf4382e0d95e7d9418d50a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
if exists(select * from sys.objects where object_id = object_id('dbo.pp_ChangeBaseCury') and objectproperty(object_id,'IsProcedure')=1) | |
drop proc dbo.pp_ChangeBaseCury | |
go | |
create procedure dbo.pp_ChangeBaseCury @companyID int, @oldCury NVARCHAR(10), @newCury NVARCHAR(10), @description NVARCHAR(255), @symbol NVARCHAR(10) AS | |
BEGIN | |
DECLARE @stmt NVARCHAR(MAX) | |
DECLARE @table NVARCHAR(255) | |
DECLARE @column NVARCHAR(255) | |
DECLARE @anyfailed bit | |
--Temp Columns | |
IF OBJECT_ID('tempdb..#tempcolumns') IS NOT NULL DROP TABLE #tempcolumns | |
CREATE TABLE #tempcolumns (column_name sysname COLLATE DATABASE_DEFAULT) | |
INSERT INTO #tempcolumns Values('Cury') | |
INSERT INTO #tempcolumns Values('CuryID') | |
INSERT INTO #tempcolumns Values('Currency') | |
INSERT INTO #tempcolumns Values('CurrencyID') | |
INSERT INTO #tempcolumns Values('BaseCuryID') | |
INSERT INTO #tempcolumns Values('FromCuryID') | |
INSERT INTO #tempcolumns Values('ToCuryID') | |
INSERT INTO #tempcolumns Values('DefaultCuryID') | |
INSERT INTO #tempcolumns Values('DestCuryID') | |
INSERT INTO #tempcolumns Values('InCuryID') | |
INSERT INTO #tempcolumns Values('OrigCuryID') | |
INSERT INTO #tempcolumns Values('OutCuryID') | |
INSERT INTO #tempcolumns Values('PriceListCuryID') | |
INSERT INTO #tempcolumns Values('ReportCuryID') | |
INSERT INTO #tempcolumns Values('RUTROTCuryID') | |
INSERT INTO #tempcolumns Values('PriceListCuryID') | |
INSERT INTO #tempcolumns Values('PriceListCuryID') | |
--Temp Tables | |
IF OBJECT_ID('tempdb..#temptables') IS NOT NULL DROP TABLE #temptables | |
CREATE TABLE #temptables (table_name sysname COLLATE DATABASE_DEFAULT, column_name sysname COLLATE DATABASE_DEFAULT) | |
INSERT INTO #temptables SELECT col.TABLE_NAME, col.COLUMN_NAME FROM INFORMATION_SCHEMA.Columns col | |
JOIN INFORMATION_SCHEMA.Tables t on t.TABLE_NAME = col.TABLE_NAME | |
WHERE col.DATA_TYPE = 'nvarchar' and t.TABLE_TYPE='BASE TABLE' | |
and col.COLUMN_NAME in (SELECT column_name FROM #tempcolumns) | |
--Validation first | |
Set @anyfailed = 0 | |
DECLARE tables CURSOR FOR SELECT table_name, column_name FROM #temptables | |
OPEN tables | |
FETCH NEXT FROM tables INTO @table, @column | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
declare @cnt int | |
set @stmt = N'select @counter=count(*) from dbo.[' + @table + '] WHERE CompanyID = ' + convert(varchar(10), @companyID) + ' AND ' + @column + ' = ''' + @newCury + '''' | |
exec sp_executesql @stmt, N'@counter int output', @counter=@cnt output | |
if @cnt <> 0 begin | |
set @stmt = 'Table [' + @table + '] already contains CuryID [' + @newCury + '] ' | |
SET @anyfailed = 1 | |
raiserror(@stmt, 16, 1) | |
end | |
FETCH NEXT FROM tables INTO @table, @column | |
END | |
CLOSE tables | |
DEALLOCATE tables | |
if(@anyfailed = 1) return | |
--Updating currency | |
DECLARE tables CURSOR FOR SELECT table_name, column_name FROM #temptables | |
OPEN tables | |
FETCH NEXT FROM tables INTO @table, @column | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @stmt = '' | |
SET @stmt = @stmt + 'UPDATE dbo.[' + @table + '] SET ' + @column + ' = ''' + @newCury + ''' ' | |
SET @stmt = @stmt + 'WHERE CompanyID = ' + convert(varchar(10), @companyID) + ' AND ' + @column + ' = ''' + @oldCury + ''' ' | |
--print @stmt | |
EXEC sp_executesql @stmt | |
--Informing Acumatica About the changes | |
SET @stmt = 'INSERT INTO WatchDog (CompanyID, TableName) values(' + convert(varchar(10), @companyID) + ',''' + @table + ''')' | |
exec sp_executesql @stmt | |
FETCH NEXT FROM tables INTO @table, @column | |
END | |
CLOSE tables | |
DEALLOCATE tables | |
Update CurrencyList set Description = @description, CurySymbol = @symbol where CuryID = @newCury | |
END | |
GO | |
begin tran | |
exec pp_ChangeBaseCury 1, 'USD' , 'TZS', 'Tanzanian shilling', 'T$' | |
--exec pp_ChangeBaseCury 1, 'TZS' , 'USD' | |
rollback tran |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment