# 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:

```sql
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: 

```sql
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: 

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

![](https://3010335096-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MjcNODv1kQRDGTj76t9%2Fuploads%2FvYykXc99CGPUVjl0xQWy%2Fimage.png?alt=media\&token=5edc1833-b1ad-4b78-b41f-5f77e8e2fa1e)

### 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: 

```sql
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.](https://3010335096-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MjcNODv1kQRDGTj76t9%2Fuploads%2F9196m28TwfX7uQMLRsXQ%2Fimage.png?alt=media\&token=62d85040-7369-47da-9a0d-d5f5ec1b1f06)

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. 

```sql
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) 
```

![](https://3010335096-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MjcNODv1kQRDGTj76t9%2Fuploads%2FELQCicUYUCtxh3qve8tN%2Fimage.png?alt=media\&token=8de7cf29-f3c4-48d7-994a-b06c9d70fc0d)<img src="https://3010335096-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MjcNODv1kQRDGTj76t9%2Fuploads%2FYqHIr6jOH9B7AUA3LW90%2Fimage.png?alt=media&#x26;token=a88431dd-79f1-4247-83e6-1f598d565f3e" alt="" data-size="original">![](https://3010335096-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MjcNODv1kQRDGTj76t9%2Fuploads%2FNYfD8qZa4cPQy31u8oKF%2Fimage.png?alt=media\&token=cd7c756b-d672-4566-a8c8-a3a90f64a0ed)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.novacura.com/extensibility-and-integration/products/database/ms-sql-server/how-to-guides/query-sql-in-odd-ways.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.