Database
SQL Server
Execute Query
6 min
query vs commands there's a basic difference between them commands modify the data, and queries retrieve the data use this action to execute an sql query in microsoft sql first of all, you'll have to enter credentials for the execute query action you can find out how to do that https //docs procesio com/sql credential how to configure the execute query action? go to the process designers section and open a new or an existing process actions configuration can be performed once they are dragged and dropped on canvas step 1 drag the execute query action from toolbar and drop it on canvas; click on the action to open the properties panel step 2 you can edit the action name step 3 select a credential https //docs procesio com/sql credential step 4 click on execute query (not configured) card to go to the configurations steps step 5 type your sql query in the editor note you can always add process variables to your query press insert/ctrl+i (windows) or cmd+i (mac) step 6 add the output variable note it has to be defined as a list\<objects> in the output variable, you will see the retrieved data/results based on the query step 7 save , validate and run the process step 8 click check instance to view the results based on our query, a list with data was retrieved in the output variable notes using sql parameters when writing sql queries in execute query , you can use sql parameters instead of inserting variables directly inside the query text parameters allow you to safely pass values from process variables to the sql query and help prevent errors caused by special characters for example, instead of writing select id, firstname, email from clients where email = '{{client email}}' you can define a parameter select id, firstname, email from clients where email = @email then map the parameter in the configuration panel shown above true 220,220,221left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type using parameters is recommended because it prevents sql errors when values contain characters like ' improves query security keeps sql statements easier to read works better with dynamic process variables if the mapped value is null , procesio automatically sends a sql null value to the database
