How to dynamically limit number of rows loaded using custom SQL

Sometimes, it’s more convenient to load only a small subset of rows in order to design a workflow. In a visual query you can just use the “Top rows” option. Here is how you can limit the number of loaded rows in a custom SQL query (for MS SQL Server):

Insert a TOP N clause using a calculated parameter.

In the attached example, the {top clause} parameter is calculated dynamically depending on parameter {Debug}. When {Debug} is ‘Y’ parameter {top clause} is set to “TOP 1000”, otherwise it’s empty.

The custom SQL has the following look:

SELECT {top clause} *  FROM   [HumanResources].[Employee]

EasyMorph dynamically inserts the value of parameter {top clause} before query evaluation. Therefore when you set {Debug} = Y the query loads only first 1000 rows:

SELECT TOP 1000 * FROM [HumanResources].[Employee]  

If {Debug} is set to something else, the query loads entire dataset:

SELECT *  FROM  [HumanResources].[Employee] 

custom-sql-top.morph (2.2 KB)