This document contains information applicable to stored procedures in most database platforms.
It also includes some tips relating specifically to Microsoft SQL Server and Microsoft Dynamics GP and NAV.
If dynamic SQL is used for SELECT purposes, users (i.e. the credentials used to access the database) must also have SELECT permission to the underlying databases / tables.
For information on working with multi-valued parameters implemented as delimited strings, see Data Retrieval Guidelines, when a delimited list of values is supplied as a parameter to a stored procedure, the procedure is responsible for processing the list.
If you use Microsoft SQL Server: In some cases, you may find that a report query seems to run slowly within Mercury, but when you try to isolate the problem in (say) SQL Server Management Studio, you can’t replicate it - performance is fine.
You may get relief by using SET ARITHABORT ON. If you scour the Internet, you can find varying opinions on when and when not to use this, but we have found it helpful in some cases with no unwanted effects. As of this writing, Microsoft indicates that ON is the default in SSMS.
Accordingly, most of the sample stored procedures provided with Mercury include a SET ARITHABORT ON statement.
For more information on SET ARITHABORT (and related settings), see here.
GP and on-premises NAV and Business central use Microsoft SQL Server as the database platform.
If your version of Mercury includes sample reports and stored procedures for use with one of these packages, then:
Those procedures are created in a dedicated Mercury On-Prem database (in the dbo schema), with EXECUTE permission granted to the database role RPTSGRP. See Database Guidelines for more information.
The queries assume that the production databases are on the same server that houses the Mercury database. You may need to modify the queries if your environment is different.
Many of the sample Mercury stored procedures use dynamic SQL as a means of creating multi-company reports from Dynamics GP data (in which each company has its own separate database on the server). You may find them useful guides for creating your own multi-company reports. As noted above, this does mean that SELECT permission is needed on the underlying tables.
To facilitate GP multi-company (i.e. multi-database queries), the sample GP reports rely on a stored procedure that executes a particular query in a given set of company databases, accumulating results along the way. The final dataset containing multi-company data is passed back to Mercury.
To handle multi-valued parameters in the form of a delimited string (for example, a list of companies passed to a stored procedure: ‘ACME, New Corp,...’), some stored procedures use a table-valued function called “Tokenize” to ensure compatibility with older versions of SQL Server.