CODEWITHSUNDEEP
mysqlstored-procedures
IrfanRaza
IrfanRaza

Reputation: 3058

Need help with MySQL query

I am trying to execute the following query statement contained within a Stored Procedure - Where all P... are parameters to SP (ex PInitialDateFrom).

SET @stmt_text = CONCAT('SELECT AccountID, Firstname as ClientName, EmailID
                 , ProductID, InitialPurchaseDate as Purchasedate 
                 FROM client_account 
                 WHERE IsRemoved = 0 
                 AND (InitialPurchasedate between ? and ?) 
                 AND ProductId IN (?)');

PREPARE stmt FROM @stmt_text;

SET @initDt1=PInitialDateFrom, @initDt2 = PInitialDateTo, @inlist=PIDs
    , @stmt_text = null;

EXECUTE stmt USING @initDt1, @initDt2, @inlist;

DEALLOCATE PREPARE stmt;

I am passing PID's as a string of ids like 1,2,3 When I try to execute the statement, only the first id is considered. For ex. 1,2,3 is used only 1 is taken, if 3,2,1 is used only 3 is taken.

Can anybody tell what's wrong with this query?

Upvotes: 2

Views: 58

Answers (2)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

You can't use this part:

and ProductId in (?)

Remember: SQL parameters are not like C macros where you are just doing string replacements. They are more than thar: When you use ?, one and only one parameter gets bound. So, when you try to bind 1,2,3 to that parameter, it's not like you are trying to bind three values but only one.

If your list is going to be of fixed size, you could use:

and ProductId in (?,?,?)

Otherwise, I don't think you will be able to use parameters for that clause. Maybe doing something like:

set @initDt1=PInitialDateFrom, 
    @initDt2 = PInitialDateTo, 
    @inlist=PIDs,  
    @stmt_text = null;
set @stmt_text = 
   concat('Select AccountID, Firstname as ClientName, EmailID, ProductID
     , InitialPurchaseDate as Purchasedate from client_account 
     where IsRemoved=0 and (InitialPurchasedate between ? and ?) 
     and ProductId in (', @inlist, ')');
prepare stmt from @stmt_text;
execute stmt using @initDt1, @initDt2;
deallocate prepare stmt;

Make sure you sanitize your input for PIDs so you don't add SQLInjection vulnerabilities to your code.

Upvotes: 2

Jaanus
Jaanus

Reputation: 16561

You should put it like this:

and ProductID = (?)

I hope it works.

Upvotes: 0

Related Questions