Reputation: 3058
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
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