can any body please Tell me basic syntax of stored procdedur

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
waseem83
Forum Commoner
Posts: 54
Joined: Tue Jun 23, 2009 3:51 am
Contact:

can any body please Tell me basic syntax of stored procdedur

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

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

Post by jayshields »

http://dev.mysql.com/doc/refman/5.0/en/ ... edure.html

That took me all of 1 minute to find using Google!
waseem83
Forum Commoner
Posts: 54
Joined: Tue Jun 23, 2009 3:51 am
Contact:

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

Post 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
Last edited by Benjamin on Wed Jun 24, 2009 10:45 am, edited 1 time in total.
Reason: Added [code=sql] tags.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

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

Post by jayshields »

Not particularly sure, are you sure that's the error message?

Have you changed the delimiter?
waseem83
Forum Commoner
Posts: 54
Joined: Tue Jun 23, 2009 3:51 am
Contact:

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

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

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

Post by jayshields »

Change the delimiter as per examples in the previous link and see what happens.
waseem83
Forum Commoner
Posts: 54
Joined: Tue Jun 23, 2009 3:51 am
Contact:

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

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

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

Post 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().
Post Reply