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
– Thom A ♦, Commented Sep 1, 2021 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
– Community Bot, Commented Sep 5, 2021 at 0: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
– B Douchet, Commented Sep 6, 2021 at 5:33

Cetin Basoz · Accepted Answer · 2021-09-02 12:55:47Z

1

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.

edited Sep 2, 2021 at 12:55

answered Sep 1, 2021 at 16:08

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

B Douchet Over a year ago

OPENJSON only works for SQL Server 2016 (13.x) and later, the doc.

Cetin Basoz Over a year ago

@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).

B Douchet Over a year ago

Please check my edit with XML parsing, but the string parameter is not understood via Excel

Cetin Basoz Over a year ago

I didn't really mean something like that. Adding a full sample.

Serg · Accepted Answer · 2021-09-01 15:56:23Z

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 1, 2021 at 15:56

Serg

22.8k5 gold badges23 silver badges50 bronze badges

2 Comments

B Douchet Over a year ago

Yes, it will work, however a,b,c is just for example, the number of parameters is unknown

Serg Over a year ago

You can build the query text with VBA at EXCEL side. Alternatively parse a csv string at Sql Server side.

B Douchet · Accepted Answer · 2021-09-03 14:53:13Z

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 3, 2021 at 14:53

B Douchet

1,0401 gold badge12 silver badges21 bronze badges

1 Comment

Cetin Basoz Over a year ago

This is not a good solution but would work in some cases. OpenXml approach is much better IMHO.

Collectives™ on Stack Overflow

Run SQL IN queries in Excel

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related