Query SQL in odd ways

Query by building a string for execution

Sometimes you need to build a query that include flow variables, like for example @userid. But if you would just add select * from table where userId like '@userid%' you would get errors.

To get around that issue you can build a string and then execute it in this way:

Declare @SQL nvarchar(max);

Set @SQL = '
Declare @UserId nvarchar (max);
SELECT sol.[SalesOrderNo]                                                 "SalesOrderNo",
       sol.[SalesOrderLineNo]                                             "SalesOrderLineNo",
       sol.[PartNo]                                                       "PartNo",
       sol.[Qty]                                                          "Qty"
FROM   SalesOrderLine sol
WHERE  sol.SalesStatusId = 10
       AND so.CreatedBy like ''' + @userId + '%'''
       
Exec (@SQL);

Select from a Procedure

Assume you have a stored procedure that executes a query that you would like to execute and get the values into Flow from. Say you have a stored procedure GetCustomers:

Create or Alter PROCEDURE dbo.GetCustomers AS
SELECT top 10 CustomerId, CustomerName 
FROM Customer

Declare a table to pass the results to, and then select from your table variable:

Declare @T Table (CustomerId nvarchar(50), CustomerName nvarchar(500)) 
Insert @T Exec GetCustomers 
Select CustomerId "CustomerId",CustomerName "CustomerName" from @T

Building a Where in-clause from rows in a column from a different data source

This time we have a customer list in another system that we are using Flow to extract from:

SELECT TOP 10 CustomerId   "CustomerId"
FROM   Customer sql

By using flow script's "Join" function (not to be confused with SQL join) we can put values of a table column into a single textual value with a given separator.

This gives us a string with: Customer1..Customer2.. etc.

With our result now in a string, we will replace the double dots with a single quote, a comma and another single quotes (',') by using char(xx). Note how the first and last char(39) is outside of the replace function to put single quotes before the first value and after the last. So our string now looks like 'Customer1', 'Customer2'..etc. And is now ready to put in our @SQL variable and to execute.

Declare @ReplaceVar nvarchar(max);
Declare @SQL nvarchar(max);

set @ReplaceVar = char(39) + replace(@SelList, '..', char(39) + char(44)+ char(39)) +char(39)

set @SQL = '
select top 10 SalesOrderNo, CustomerId
from salesorder so
WHERE so.CustomerId in (' + @ReplaceVar + ')'

exec (@SQL)