Dynamic select variables in SQL

Question

Is it possible to have a variable number of elements selected in a single SQL statement? For eg. based on the number of parameters that have values, those are selected in the statement. If the statement is:

"Select x, y, z from table A where abc='123';"

and x, y and z are values being passed over from a previous page. So if only x and z have values then the statement should work like:

"Select x, z from table A where abc='123';"

and if only x has a value, it should select:

"Select x from table A where abc='123';"

Is this possible to do?

"x, y and z are values being passed over from a previous page" - what are you talking about? What "previous page"? — Oded
– Oded, Commented Jan 29, 2012 at 20:00
I mean i am using PHP and i want to give the users to select the fields they would like to view so i want the statement to display results based on what the user selects. I will pass these as parameters into the database function to retrieve the results accordingly. — codingNewbie
– codingNewbie, Commented Jan 29, 2012 at 20:05
Are you trying to do this with pure SQL or with an application server or a stored procedure? — Brian Hoover
– Brian Hoover, Commented Jan 29, 2012 at 20:05
where are you calling your sql statement from? if you are building the sql statement in for example php, then of course you can code up some logic to selectively create a statement based on the values of variables. — slapthelownote
– slapthelownote, Commented Jan 29, 2012 at 20:06
SQL and PHP.. so i will pass in PHP variables. I just want to know how to make the select statement dynamic. can it be done? — codingNewbie
– codingNewbie, Commented Jan 29, 2012 at 20:06

Bill Martin · Accepted Answer · 2012-01-29 20:08:41Z

0

If you are building it from a web page, then just modify the select statement using your code to pass to the SQL server.

If you are doing this in SQL (stored proc or something) and passing in which columns to be selected, then yes, you could build the select statement dynamically as a variable. Then execute the variable. It's possible but NOT recommended.

var statement = "select " + @columns + " from table where abc='123'" exec @statement

Obviously, use caution if you are using this on a web page to avoid SQL injection.

answered Jan 29, 2012 at 20:08

Bill Martin

4,96310 gold badges57 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

codingNewbie Over a year ago

I am using PHP and mysqli for the database connection.. will the column names be an array or something?

Bill Martin Over a year ago

if you are using php, just use php to dynamically create the statement. See here : stackoverflow.com/questions/4704051/…

Sagi · Accepted Answer · 2012-01-29 20:08:55Z

0

The correct approach would be to concatenate the SQL statement according to the user's request, or hide columns in code. Trying to do it in an SQL statement is not the right way.

answered Jan 29, 2012 at 20:08

Sagi

1,00913 silver badges15 bronze badges

Comments

nolt2232 · Accepted Answer · 2012-01-29 20:09:55Z

0

If you are dynamically building a string of sql, just put some conditional logic in your PHP that looks to see what params were passed in and use the appropriate SQL.

answered Jan 29, 2012 at 20:09

nolt2232

2,6441 gold badge24 silver badges35 bronze badges

Collectives™ on Stack Overflow

Dynamic select variables in SQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related