Jump to content

Stored Procedure - passing in order by parameter


mds1256

Recommended Posts

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

Link to comment
Share on other sites

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');

Link to comment
Share on other sites

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 ; 

Link to comment
Share on other sites

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 ; 

Link to comment
Share on other sites

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))

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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')

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.