Day: May 10, 2017

SQL Script To Return Functional Currencies For All Companies Without a Cursor

Ian Grieve2 minute read4 Comments

Microsoft Dynamics GPI posted a script a while ago which used a cursor to return the functional currencies for all companies connected to a system database. However, I have recently revisited this script and created a version which does not use a cursor.

This script has been written to only return the companies which do not have a functional currency set; if you want to see all companies, regardless of the functional currency, remove the highlighted section.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TABLE #FunctionalCurrencies(
	INTERID VARCHAR(5)
	,FUNLCURR VARCHAR(20)
)
GO

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
					SELECT 
						CHAR(13) 
							+ 'SELECT 
								''' + INTERID + '''
								,FUNLCURR
							FROM
								' + INTERID + '.dbo.MC40000
							WHERE
								LEN(FUNLCURR) = 0'
					FROM
						DYNAMICS.dbo.SY01500
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #FunctionalCurrencies
	EXEC sys.sp_executesql @SQL
GO

SELECT * FROM #FunctionalCurrencies
GO

DROP TABLE #FunctionalCurrencies
GO