Why Does My Query Break When It Is Parameterized?

I have 2 tables - Sales and Product. Sales can store the product as Idn or Name (legacy design) and the Type column specifies the actual type associated to it. Product etc. is a su

Solution 1:

SQLAlchemy generates this SQL script with your non-parameterized query (select_adhoc):

SELECT * FROM products
JOIN sales ON products.idn = sales.pid
AND sales.type = 'number'WHERE products.idn in (1);

But with the parameterized query (select_parametrized), it generates this: (I checked from SQL Server Profiler.)

declare@p1intset@p1=NULLexec sp_prepexec @p1 output,N'@P1 nvarchar(12),@P2 int',N'
SELECT * FROM products
INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = @P1
WHERE products.idn in (@P2);
',N'number',1select@p1

If you try this on SQL Server you will get the same exception:

Msg 8114, Level 16, State 5, Line 32 Error converting data type varchar to numeric.

The problem is at the @P1 parameter declaration -- it makes an implicit conversion to varchar (the type of sales.type) and that causes this problem. Probably Python 2 generates varchar?

If you change your query like this it will work correctly; or you need to change the type of sales.type to nvarchar.

select_parametrized = """
SELECT * FROM products
INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = CAST(? AS VARCHAR(50))
WHERE products.idn in (?);
"""