Stored Procedure - passing in order by parameter

[mds1256]

Hi

 

Struggling with the following:

 

I am wanting to build a dynamic stored procedure to allow you to pass in parameters for the order by column

 

e.g.

 

select * from users order by id desc

 

I want to use the ID part as a parameter so I would call as follows:

 

call selectUsers('id')

 

but when I do that it doesnt apply the descending sort... I am sure I am missing something.

 

Can any one help me with this one?

 

Thanks

[jazzman1]

Can you post out your procedure code ?

[fenway]

Why it this an SP to begin with/

[mds1256]

Why it this an SP to begin with/

 

because the same query is used in many places within the site.

[mds1256]

Can you post out your procedure code ?

 

example code that doesnt seem to work

 

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_selectNewsArticles`(IN fld VARCHAR(255))
BEGIN
select id, newsTitle from tbl_newsArticles order by fld desc;
END

 

call sp_selectNewsArticles('id');

[jazzman1]

Okay, drop down your old stored procedure and create a new.

You need to define a table_name, not column's name.

Try this:

DELIMITER //
DROP PROCEDURE IF EXISTS `sp_selectNewsArticles`;
CREATE PROCEDURE `sp_selectNewsArticles`(IN `tbl_newsArticles` VARCHAR(255))
BEGIN
SELECT `id`, `newsTitle` FROM `tbl_newsArticles` ORDER BY fld DESC;
END //
DELIMITER ; 

[mds1256]

Don't think this is what I want to achieve

 

I want the dynamic part (the IN parameter) to replace the fld field in the SQL code, the table will stay the same

 

 

 

Okay, drop down your old stored procedure and create a new.

You need to define a table_name, not column's name.

Try this:

DELIMITER //
DROP PROCEDURE IF EXISTS `sp_selectNewsArticles`;
CREATE PROCEDURE `sp_selectNewsArticles`(IN `tbl_newsArticles` VARCHAR(255))
BEGIN
SELECT `id`, `newsTitle` FROM `tbl_newsArticles` ORDER BY fld DESC;
END //
DELIMITER ; 

[jazzman1]

Don't think this is what I want to achieve

 

I want the dynamic part (the IN parameter) to replace the fld field in the SQL code, the table will stay the same

 

Yes, but your IN statement is wrong ->

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_selectNewsArticles`(IN fld VARCHAR(255))

must be 

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_selectNewsArticles`(IN `tbl_name` VARCHAR(255))

[mds1256]

Not according to this

 

http://www.mysqltutorial.org/stored-procedures-parameters.aspx

 

It should be a temp parameter name which can then be used in the procedure.

[jazzman1]

Hm... I've never used column's name in this statement.

I'm going to check in my database 

PS. By the way, did you check out my solution ?

[jazzman1]

Try this:

DELIMITER //
DROP PROCEDURE IF EXISTS `sp_selectNewsArticles`;
CREATE PROCEDURE `sp_selectNewsArticles`(IN `fld` VARCHAR(255))
BEGIN
SELECT `id`, `newsTitle` FROM `tbl_newsArticles` WHERE `col_name` = `fld` ORDER BY fld DESC;
END //
DELIMITER ; 

 

P.S Just add a WHERE clause , I don't know your database structure

[jazzman1]

Ah, now I've got it, you don't have in table structure `fld`, right ?

Your query it seems like this:

SELECT `id`, `newsTitle` FROM `tbl_newsArticles` ORDER BY 1 DESC

[mds1256]

Nope, still doesnt work.

 

I am wanting to substitute the field name not a value, so for example:

 

call sp_selectNewsArticles('fieldNameHERE')

 

would actually run the following SQL:

 

select id, newsTitle from tbl_newsArticles order by fieldNameHERE desc;

 

 

Try this:

DELIMITER //
DROP PROCEDURE IF EXISTS `sp_selectNewsArticles`;
CREATE PROCEDURE `sp_selectNewsArticles`(IN `fld` VARCHAR(255))
BEGIN
SELECT `id`, `newsTitle` FROM `tbl_newsArticles` WHERE `col_name` = `fld` ORDER BY fld DESC;
END //
DELIMITER ; 

 

P.S Just add a WHERE clause , I don't know your database structure

[mds1256]

Ah, now I've got it, you don't have in table structure `fld`, right ?

Your query it seems like this:

SELECT `id`, `newsTitle` FROM `tbl_newsArticles` ORDER BY 1 DESC

 

the query should be:

 

select id, newsTitle from tbl_newsArticles order by id desc

[jazzman1]

Okay, but when you called ->  call sp_selectNewsArticles(1);

Your query is gonna be :

SELECT `id`, `newsTitle` FROM `tbl_newsArticles` ORDER BY 1 DESC

b/s 

SELECT `id`, `newsTitle` FROM `tbl_newsArticles` ORDER BY fld DESC

[mds1256]

Okay, but when you called ->  call sp_selectNewsArticles(1);

Your query is gonna be :

SELECT `id`, `newsTitle` FROM `tbl_newsArticles` ORDER BY 1 DESC

b/s 

SELECT `id`, `newsTitle` FROM `tbl_newsArticles` ORDER BY fld DESC

 

yes.

 

 

I have managed to hack about and do what I am after with the concat function, is there not an easier way to achieve this

 

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE sp_selectNewsArticles(IN input VARCHAR(255))
BEGIN

SET @query = CONCAT('SELECT id, newsTitle FROM tbl_newsArticles order by ',input,' desc');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

 

 

call sp_selectNewsArticles('id')

 

[jazzman1]

I have managed to hack about and do what I am after with the concat function, is there not an easier way to achieve this

I don't know, someone having more experience have to say..

[mds1256]

I have managed to hack about and do what I am after with the concat function, is there not an easier way to achieve this

I don't know, someone having more experience have to say..

 

Ok, thanks very much for your time.

[fenway]

No, there isn't -- SPs don't support dynamic SQL.  And they're not supposed to.  The whole concept is that the server can optimize the query -- but you've just made that impossible -- and that's why it's not supported.

[fenway]

Why it this an SP to begin with/

 

because the same query is used in many places within the site.

That's not a good reason -- do you use connection pooling?

[mds1256]

 

That's not a good reason -- do you use connection pooling?

 

Not really sure what you mean so I would say no.

 

the use of stored procedures is something that I have read removes the possibility of a SQL injection, I am not using them for everything but for crucial things like username and password checking etc.

 

Is there a better way of preventing a SQL injection rather than using Stored Procedures?

[Christian F.]

What you're thinking of is probably Prepared Statements, not Stored Procedures. That's, at least, the most common way to prevent SQL injections, and it'll allow you to construct the query using a scripting/programming language which is better suited for what you want to do.