Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 stored procedures:

CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500))
BEGIN    
IF (param_CRITERIA <> "") THEN
SET @QUERY1 = concat('SELECT user_name,count(*) as count 
  FROM foodmaster d inner join user_details u 
  on d.user_id=u.user_id WHERE (', param_CRITERIA ,') group by u.user_id;');    
PREPARE stmt FROM @QUERY1;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;    
END IF;


CREATE PROCEDURE `sample`(param_CRITERIA VARCHAR(500))
BEGIN
SELECT user_name,count(*) as count FROM foodmaster d 
  inner join user_details u on d.user_id=u.user_id 
  WHERE ( param_CRITERIA ) group by u.user_id;
END;

I tried with same param_criteria in both SPs. In the first procedure, i am getting results. But not in second. Why?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

Because the second one is, coincidentally, syntactically valid but not the correct way to do what you are doing. You are trying to do dynamic sql without using dynamic sql. What you are doing in #2 is actually putting the literal string ', param_CRITERIA ,' in the where clause, not the content of that variable.

The only way to do what you are trying to do is method #1.

I would also point out that if the value you're passing in method #1 is coming from an untrustworthy source (such as a web form or url url query string) then you are also potentially setting yourself up for an SQL injection vulnerability.

Edit:

You have updated your question so that example number 2 is no longer consistent with my answer, above, so for future clarity, I'll point out that your original query #2 looked like this:

... WHERE (,'param_CRITERIA',) group ...

Now you have removed the commas and the quotes:

... WHERE ( param_CRITERIA ) group ...

This does not work either, because, as I have pointed out, you are trying get the contents of a variable interpolated into the body of the query, blurring the boundary between logic and data. The only way to do this is to craft a dynamic SQL query, which is what you're doing in #1. Example #2 is not a dynamic query and cannot be made to be one.

share|improve this answer
    
Thanks @Michael for your quick reply. I changed (', param_CRITERIA ,') to param_CRITERIA. Now it should take the value in varaiable, rt?.. But data is still not getting –  Praveen Prasannan May 14 '13 at 5:34
    
I have edited second procedure. –  Praveen Prasannan May 14 '13 at 10:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.