How Do I Use an SQL IN Clause as a Parameter

Question

I have tried to compose an SQL IN clause using python and pandas and using the SQL profiler I find the following is passed from my python program to SQL server but no data is returned - I assume it is generating an internal error because of bad formatting but I can not identify the issue:

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 nvarchar(22)',N'
               SELECT [Date], [Person], [Amount]
               FROM [Logs].[dbo].[Results]
               WHERE [Date] >= GETDATE()-1 AND [Person] IN @P1',N'(''person1'', ''person2'')'
select @p1

Can anyone help me identify what is wrong?

What is the python code you're executing? – cs95 Sep 05 '18 at 21:01 — cs95, Sep 05 '18 at 21:01

score 0 · Answer 1 · answered Sep 05 '18 at 21:30

You can try a bunch of different things here in order to diagnose the problem:

Save your query to a string variable, print it, and run it against the database.
If the above step returns results, then the problem is not with your query. I would then check the database configuration you have in place in your python script/configuration file.
If the db configuration seems correct, add a try and except block to capture more details regarding any exception that occurs.

If you are using pyodbc check out the following link to see how you can handle errors:

how to catch specific pyodbc error message

score 0 · Answer 2 · answered Sep 06 '18 at 00:46

0

I don't believe you can specify an "IN" string as a parameter like that. This however will work in sql server. If its not sql find the similar function, or write your own.

AND [Person] IN (select * from string_split(@P1,','))

answered Sep 06 '18 at 00:46

TomC

2,759
1
7
16

I can't use that function because of the DB compatability level, and I can't change that either. – beliskna Sep 06 '18 at 08:03
There are a bunch of functions you can write to do the same thing - just search for splitting a CSV list into a table. Then use that instead. – TomC Sep 06 '18 at 09:08

score 0 · Answer 3 · answered Sep 06 '18 at 08:18

0

After some review and a quick chat with the dbadmin I have decided that it is simply easier for me to use python to dynamically build my query. Thanks for your efforts.

answered Sep 06 '18 at 08:18

beliskna

185
11

How Do I Use an SQL IN Clause as a Parameter

3 Answers3