Executing variable SQL statements

Official translation unavailable. Translate this page with Google:

Problem

When executing SQL statements on the SQL servers you might want to execute a variable SQL statement. This SQL statement is not static, but you generate it on the fly in the code. You want to query data from different tables, extract different fields and provide different criteria.

Solution

When using Microsoft SQL Servers you can use the EXEC() function to execute a variable SQL Statement. Construct your variable SQL statement and then execute it.

Option 1: Constructing the variable SQL statement inside Execute SQL Statement action

DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT * from Device WHERE DeviceID = 1';
EXEC(@query);

Option2 : Constructing the variable SQL statement in Execute script action (VBScript/Python syntax) and executing it in Execute SQL Statement action

VBScript:

query = "SELECT * FROM device WHERE DeviceID = 1"

SQL query:

EXEC(:query);