Page 1 of 1

can any body please Tell me basic syntax of stored procdedur

Posted: Wed Jun 24, 2009 8:39 am
by waseem83
i want to make a stored procedure for only select query which will take three parameters
1-tableName
2-strColumns
3-strWhere

These three parameters will be passed into query and query will be executed.

PLease tell me basic syntax for that.]
i will be very thank ful to u .
Waseem.

Re: can any body please Tell me basic syntax of stored procdedur

Posted: Wed Jun 24, 2009 8:50 am
by jayshields
http://dev.mysql.com/doc/refman/5.0/en/ ... edure.html

That took me all of 1 minute to find using Google!

Re: can any body please Tell me basic syntax of stored procdedur

Posted: Wed Jun 24, 2009 9:01 am
by waseem83
Can you please tell me whats wrong with that procedure?
I will be very very thank ful to you if you give me solution..

The error i get after executing is

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

THe script is....

Code: Select all

 
CREATE PROCEDURE SelectTable(IN strTableName VARCHAR(20),IN strColumns VARCHAR(100), IN strWhere VARCHAR(100),IN strOrderBy VARCHAR(100), IN strGroupBy VARCHAR(100),IN nLimit VARCHAR(100),IN nLimitEnd VARCHAR(20))   
BEGIN
IF strWhere IS NOT NULL
then
         SELECT DISTINCT strColumns FROM strTableName WHERE strWhere;
        else
        SELECT DISTINCT strColumns FROM strTableName;
             END IF;
    
        IF strGroupBy IS NOT NULL
then
            strQry += GROUP BY strGroupBy;
            END IF;
 
        IF strOrderBy IS NOT NULL
then
            strQry += ORDER BY strOrderBy;
           END IF;
     
        IF nLimitEnd IS NOT NULL
then
            strQry += LIMIT nLimit,nLimitEnd;
 
              END IF;
        IF nLimit IS NOT NULL
then
            strQry += LIMIT nLimit;
              END IF;
    }
END

Re: can any body please Tell me basic syntax of stored procdedur

Posted: Wed Jun 24, 2009 10:32 am
by jayshields
Not particularly sure, are you sure that's the error message?

Have you changed the delimiter?

Re: can any body please Tell me basic syntax of stored procdedur

Posted: Thu Jun 25, 2009 2:05 am
by waseem83
Yes i am sure that is the error message. Please make it correct for me i will be very thank ful to you.
No i didnt change delemiter.

Re: can any body please Tell me basic syntax of stored procdedur

Posted: Thu Jun 25, 2009 5:48 am
by jayshields
Change the delimiter as per examples in the previous link and see what happens.

Re: can any body please Tell me basic syntax of stored procdedur

Posted: Thu Jun 25, 2009 6:11 am
by waseem83
I created procedure as follows its working fine for the parameter strWhere but its not working whith strTableName.

CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`(IN strWhere VARCHAR(50),IN tableName VARCHAR(50))
select * from tableName where first_name=strWhere


The PHP code with which i called it is
<?PHP
$mysqli = new mysqli( "localhost", "root", "", "test1" );
$res = $mysqli->multi_query( "CALL p2('Joey','users');" );
?>


If i pass only column value its working fine i.e p2('Joey')
But with both arguments its not working fine.

Please help me about this.

Re: can any body please Tell me basic syntax of stored procdedur

Posted: Thu Jun 25, 2009 7:29 am
by jayshields
To be honest I'm not so familiar with SPs. It's probably either an issue with to do with missing backticks/quotes, or reserved words.

How do you know it's working fine or not working? I don't know if parameters in SPs are optional, I would guess not - so I don't know how it's working with a single parameter. Besides, if you pass a single parameter, and optional parameters do actually work, then you're implying that you've got a table called "tableName" for your query to execute successfully - which I doubt.

You should probably look at some examples of SPs via Google, and then probably look at some examples of executing them via the PHP manual page for multi_query().