# SQL Server - Select actions
# Select rows
This action lets you select rows based on certain criteria defined by a
WHERE condition. Rows from the selected table that match the
WHERE condition will be returned as the output of this action.
Select rows action
First, select a table/view to work with. This can be done either by selecting a table or view from the pick list, or toggling the input to text mode and typing the full table/view name.
# WHERE condition
Next, provide a
WHERE condition to filter rows. This condition can be as simple as filtering a single record by it's
ID = 123
Alternatively, it can be used to select multiple rows based on values in one or more columns.
status = 'closed' AND priority = 1
WHERE conditions with subqueries can also be used. Refer to the
WHERE condition guide for more information.
Used in conjunction with your
WHERE condition to prevent SQL injection. After declaring named bind variables in your
WHERE input, assign values to these variables using this input field. Learn more
# Order by
Rows returned from this action can be ordered based on the Order by input field. This field is used to change the default ordering of rows from your SQL Server database.
You can also define the direction of order for each column you wish to order by. The following order by statement will order rows by
priority in ascending order followed by
created_date in descending order (latest first).
priority asc, created_date desc
This input field determines the maximum number of rows to return. The default limit is 100 and capped at a maximum of 50000 rows for a single Select rows action. If you require to select more than 1000 rows and perform actions, this can be done in batches from Workato recipes directly. Find examples in our use cases.
This input field gives you the option to fetch only a page of results from the entire results set. For example, to skip the first 100 rows of the selected results set, input
100 to this field. The default is
# Select rows using custom SQL
This action lets you select rows based on a custom SQL query. Rows that are returned from the query will be returned as the output of this action. Check out some best practices when using custom SQL actions
# Supported versions
This action is only supported for SQL Server 2012 or newer. It uses a default stored procedure
sp_describe_first_result_set that is only available from SQL Server 2012 onwards.
Select rows using custom SQL action
Provide the SQL to be executed to select rows. The SQL here will be used to generate the output datatree. To do this, the SQL will be executed once when you provide it. You can map datapills here to execute dynamically changing SQL statements. Remember to wrap datapills in quotes (
''). Do not add a
; at the end of your SQL query as this will cause the step to fail
Avoid using limit clauses like
TOP in your SQL. This is because the limit to the number of rows returned in the query is based on the value defined in the Limit input field. Adding your own limit clause will cause the action to fail.
This input field determines the maximum number of rows to return. The default limit is 100 for a single Select rows using custom SQL action.
If this field is left blank,
TOP 100 will be used.
Last updated: 11/17/2021, 9:02:18 PM