Mysql passing string into a parameter

Hi guys,

Is there any way possible to simply create a parameter,
and  Call pass it a string like this.




DELIMITER $$
DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test`(IN wherestr VARCHAR(255))
BEGIN
      select *
      from summary
      where  wherestr
      group by storename;
END $$
DELIMITER ;

CALL `test`('storenumber = 2')
LVL 8
kingjelyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KalpanCommented:
Please refer the follwoing

DELIMITER $$
DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test`(IN wstr VARCHAR(255))
BEGIN
      select *
      from summary
      where  wherestr = wstr
      group by storename;
END $$
DELIMITER ;

CALL `test`('2')
kingjelyAuthor Commented:

Hi Kmax
I get, Unknown column 'wherestr' in where clause
theGhost_k8Database ConsultantCommented:
As error suggests you don't have wherestr as a field...
You can ofcourse pass the string, just replace the FIELDNAME that you want to compare with the passed parameter.

 select *       from summary      where  FIELDNAME = wstr       group by storename;
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

kingjelyAuthor Commented:

Hi again,

Kate,
With this, I want to just create an empty parameter and call pass it a string, which the procedure excecutes.

LIKE this even, (with no where clause, just the `wherestr`)



DELIMITER $$
DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test`(IN wherestr VARCHAR(255))
BEGIN
      select *
      from summary
      wherestr
      group by storename;
END $$
DELIMITER ;

call  `wherestr'(WHERE STORENUMBER =2 and BRAND NUMBER = 2)

I hope that makes sense to you =D
kingjelyAuthor Commented:
sorry that call should be

call  `test3'(WHERE STORENUMBER =2 and BRAND NUMBER = 2)
kingjelyAuthor Commented:
SOrry again,

call  `test'(WHERE STORENUMBER =2 and BRAND NUMBER = 2)
KalpanCommented:
you can't add the query parameters with fields here like the above comment from you...

instead...do it like ...you can take INT instead of VARCHAR

DELIMITER $$
DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test`(IN storno  VARCHAR(255), brandno  VARCHAR(255))
BEGIN
      select *
      from summary
      WHERE STORENUMBER =storno and BRAND NUMBER = brandno
      group by storename;
END $$
DELIMITER ;

call  `test`('2','2')
flytox06Commented:
Then use :


DELIMITER $$
DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test`(wherestr VARCHAR(255))
BEGIN
        SET @strQuery = CONCAT("SELECT * FROM summary ", wherestr);
        PREPARE stmtquery FROM @strQuery;
        EXECUTE stmtquery;
        DEALLOCATE PREPARE stmtquery;
END $$
DELIMITER ;


More info about prepared statement, here : http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kingjelyAuthor Commented:

Hi Kmax, I understand somewhat how to do simple stored procedure and call,
BUT, I want to type the where clause into the call `where`(parameter)

call `test`('`storenumber` =2 and `brandnumber` = 2');


actually i will not be typing it in, it will be passed from another application.
The application will pass into call the `where` clause, based on a list box where a user ticks which stores, brands, departments ect they wish to include in a report.

For what i need to know,

can you call pass the whole where clause, in the parameter, as a string,



kingjelyAuthor Commented:
Hi Flytox06,

I think this is what i need,

could you include the call i would need.

I have this
call `test`('`storenumber` =2 and `brandnumber` = 2');
but it just passes EVERYTHING , not just store2, and brand 2.
kingjelyAuthor Commented:
Thanks GUYS
flytox06Commented:
if you call test("storenumber =2 and brandnumber = 2"); then it will exactly launch :

SELECT * FROM summary storenumber =2 and brandnumber = 2 (which is a wrong syntax).

So, modify the prepared statement to include WHERE after summary or simply call :

test("WHERE storenumber =2 and brandnumber = 2");

If I where you, I would add WHERE after the summary keyword. And be careful to sql injection...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.