Run SQL IN queries in Excel

Question

Objective

I want to run SQL queries with IN statement into an Excel file and using dynamic parameters.

The queries

First, it can be:

SELECT feature1 
FROM table 
WHERE feature2 IN (?) 

or

SELECT feature1 
FROM table 
WHERE feature2 IN ?

The second one has systematic error whatever the parameters

Syntax error or access violation

So I focused on the first one.

The Parameters

I am having problems to specify the list of parameters (Data -> Request and connection -> Connections -> Definition -> Parameters) :

'a','b','c' -> returns Nothing
"a","b","c" -> returns Nothing
"'a','b','c'" -> returns Nothing
'a' -> returns Nothing
a -> returns Something
a,b,c -> returns Nothing

Last but not least, when it's hard encoded, this returns Something:

SELECT feature1 
FROM table 
WHERE feature2 IN ('a', 'b', 'c')

Questions

What's happening ? How can I specify dynamically multiple parameters ? Is there a way to visualise this built query to check what's wrong? Or any techniques to avoid the problem ?

Preferably, without VBA

Using Excel 2019 and Microsoft SQL Server 2012 (No STRING_SPLIT, OPENJSON)

EDIT

Based on the answers and on this post, I tried with this sql query to avoid using STRING_SPLIT and OPENJSON:

SELECT feature1 
FROM table 
WHERE feature2 IN (
    SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
    FROM (
         SELECT CAST('<X>'+REPLACE( ? , ',', '</X><X>')+'</X>' AS XML) AS String
         ) AS A
    CROSS APPLY String.nodes('/X') AS Split(a)
);

However, it doesn't understand the parameter via Excel, while it works when it's hard coded in the query.

You can't pass an `IN` with a single [scalar] parameter and expect the value to be treated like an array; array's aren't a data type that exist in SQL Server. One method, instead, is to use a Table-Type parameter and `JOIN` to it instead. Otherwise you *could* use a delimited string and split the value, but as you're on SQL Server 2012 then you'll have no access to `STRING_SPLIT` so would need to use a *good* **set-based** udf. — Thom A, Sep 01 '21 at 15:55
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Sep 05 '21 at 00:18
I don't understand what's hard to understand: all the answers are related and relevant to my post, and they helped me. So I think my question has been well understood, however if you insist to add details I'll do it — B Douchet, Sep 06 '21 at 05:33

Cetin Basoz · Answer 1 · 2021-09-02T12:55:47.587

You cannot directly create an IN query. However, you could send your parameters as comma delimited values or xml or json string to be parsed into a table at server side. Here is a simple example doing it using Northwind sample database + json, The query is:

SELECT * FROM [Northwind]..[Products]
WHERE ProductID IN
(
    SELECT value FROM OPENJSON(@Json)
)
ENDTEXT

'  A Json array of integers as parameter
Json = "[1, 3, 5, 6]"

EDIT: Sample with XML:

Sub test()
Dim query As String
Dim myParameters As String

query = "DECLARE @hDoc int" & vbCrLf & _
"exec sp_xml_preparedocument @hDoc OUTPUT, ?" & vbCrLf & _
"SELECT * FROM [Northwind]..[Products]" & vbCrLf & _
"WHERE ProductID IN" & vbCrLf & _
"(SELECT id FROM OPENXML(@hDoc, '/root/row', 1) WITH (id int))" & vbCrLf & _
"EXEC sp_xml_removedocument @hDoc"

myParameters = "<root>" & vbCrLf & _
        "<row id=""1""/>" & vbCrLf & _
        "<row id=""3""/>" & vbCrLf & _
        "<row id=""5""/>" & vbCrLf & _
        "<row id=""8""/>" & vbCrLf & _
        "<row id=""9""/>" & vbCrLf & _
    "</root>"
    

    Dim connectionstring As String
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim p As ADODB.Parameter
    Dim rs As ADODB.Recordset



    connectionstring = "Provider=SQLNCLI11.0;Data Source=.\SQLExpress;" & _
                  "Initial Catalog=Northwind;" & _
                  "Integrated Security=SSPI;"

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    cn.Open connectionstring

cmd.ActiveConnection = cn
cmd.CommandText = query
Set p = cmd.CreateParameter("myValues", adVarChar, adParamInput, -1)
p.Value = myParameters
cmd.Parameters.Append p

' Execute command
Set rs = cmd.Execute
Sheets(1).Range("A1").CopyFromRecordset rs
End Sub

PS: I am not a VB nor VBA guy, I would really do this with another language but anyway this works too.

EDIT2: BTW this sends:

exec sp_executesql N'DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT, @P1
SELECT * FROM [Northwind]..[Products]
WHERE ProductID IN
(SELECT id FROM OPENXML(@hDoc, ''/root/row'', 1) WITH (id int))
EXEC sp_xml_removedocument @hDoc',N'@P1 varchar(max)','<root>
<row id="1"/>
<row id="3"/>
<row id="5"/>
<row id="8"/>
<row id="9"/>
</root>'

To SQL server. You could as well use sp_executesql yourself and create a simpler IN query, BUT that might be dangerous if you don't have a good control of the code generated.

`OPENJSON` only works for *SQL Server 2016 (13.x) and later*, the [doc](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15). — B Douchet, Sep 02 '21 at 05:52
@BDouchet, as I said you can also use a comma separated value or xml if you like. Code is different though. If you need I can give you an XML sample as well (or even csv). — Cetin Basoz, Sep 02 '21 at 11:18
Please check my edit with XML parsing, but the string parameter is not understood via Excel — B Douchet, Sep 02 '21 at 11:46
I didn't really mean something like that. Adding a full sample. — Cetin Basoz, Sep 02 '21 at 12:53

score 0 · Answer 2 · answered Sep 01 '21 at 15:56

0

You must specify the list of parameters and assign values accordingly. For example for a,b,c you must create 3 parameters of the query

SELECT feature1 FROM table WHERE feature2 IN (?,?,?)

A parameter is a single value.

answered Sep 01 '21 at 15:56

Serg

22,285
5
21
48

Yes, it will work, however `a,b,c` is just for example, the number of parameters is unknown – B Douchet Sep 01 '21 at 15:59
You can build the query text with VBA at EXCEL side. Alternatively parse a csv string at Sql Server side. – Serg Sep 01 '21 at 16:04

score 0 · Accepted Answer · answered Sep 03 '21 at 14:53

0

ok, based on the answers and this post, this method enables to set multiple parameters (and unkmown size) in a sql query with excel. It doesn't use IN statement but the result is the same.

The SQL :

SELECT feature1 
FROM database
WHERE ?
LIKE '%,' + feature2 + ',%' ;

Here the ? has to looks like ',a,b,c,'

The Excel:

="'," & TEXJOIN(",",TRUE,A:A) & ",'"

This is not optimised for numerous rows, so an optimised solution is also welcome

answered Sep 03 '21 at 14:53

B Douchet

970
1
9
20

1

This is not a good solution but would work in some cases. OpenXml approach is much better IMHO. – Cetin Basoz Sep 05 '21 at 15:02

Run SQL IN queries in Excel

3 Answers3