dynamic populate SELECT statement with variables

dynamic sql with variables
using variable in dynamic sql query
variables inside dynamic sql
dynamic query in sql server with parameters
assign exec result to variable sql server
dynamic sql variable in where clause
how to store select query result in variable in sql server
dynamic select statement

I have a table with 7 columns populated from mysql... the table headers each contain a checkbox which if checked and clicked on submit will be sent to a export to csv page.

Is there a way to use the SELECT $var1, $var2...$var7 FROM... ? $var1 to $var7 will be used if checkbox checked and if no checkbox checked then display some message

To betted understand what i am asking i'll put some code...

HTML

<td class="captabel">
 <input type="checkbox" value="expid" >
</td>
<td class="captabel">
 <input type="checkbox" value="exploc" >            
</td>
etc

php

if(isset($_POST['expid'])){
$expid="id";
}
else{
$expid="";
}
if(isset($_POST['exploc'])){
$exploc="locatie";
}
else{
$exploc="";
}
etc

and at some point comes the SELECT * FROM table... <-- this is what i want to replace with SELECT $expid, $exploc ... FROM table... i have tried various ways and none worked. Thanks.

You have to use the name attribute on your checkbox

<input type="checkbox" value="123" name="expid" />

Table Variables In T-SQL, How do I assign a dynamic query result to a variable in SQL Server? This means that it is used now only for the most complex dynamic SQL scenarios, generally categorized as “method 4” dynamic SQL, which is when, at the time of compilation, you don’t know either the number of columns in your SELECT list or the number of bind variables in your statement.

  1. Modify your input type <input type="checkbox" name="exploc" value="exploc">
  2. In your php script check :

    $selectStr= "";
    if(!empty($_POST['expid'])) {
      $selectStr="id";
    }
    else{
     $selectStr="";
    }
    if(!empty($_POST['exploc'])) {
      $selectStr= !empty($selectStr) ? ", locatie" : "locatie";
    }
    else{
     $selectStr="";
    }
    

so your query will be

    if(!empty($selectStr)) {
      $qry = "SELECT $selectStr FROM table...";
     }

Instead of setting different name you can set array type name in <input type="checkbox" value="exploc" name="exploc[]">

Using Variables in Dynamic SQL, In this blog post, you are going to see step by step how to use variables in Dynamic SQL which are extremely useful in SQL scripts. Build select statement dynamically and use it to populate a stored procedure variable. I am trying to get count of a rows with specific values in a table, and if the count is 0, then add a value in the table. This count is a local variable in stored procedure. I am building the SQL dynamically and storing SQL statement into a nvarchar variable.

Solved it.

Based on Dipanwita Kundu answer i used

if(!empty($columns)){
    $sql = 'SELECT '. implode(', ', $columns). ' FROM raport' ;
}   

and works like a charm. Thank you.

SQL SERVER - Results of Dynamic SQL Into A Variable, For example here is the dynamic SQL which I am executing with the help of sp_executeSQL. When you run the procedure, you get the necessary  If the SELECT statement returns more than one value, the variable is assigned the last value that is returned. If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL. One SELECT statement can initialize multiple local variables.

dynamic sql, You were missing the variable assignment inside the dynamic SQL batch. DECLARE @sqlcommand NVARCHAR(MAX) = 'SELECT TOP 1  They are especially useful in dynamic SQL environments. They can, and are often used as counters for loops to control how many times you want the code inside the loop to run. A statement can use it to test the value of the data, or a stored procedure can use it to return a value. We can use the DECLARE statement to declare one or more variables.

So You Need To Fill a Dropdown Dynamically, The options in the second dropdown will be dynamically replaced. Now we can query that database, in the “dd_vals” table for entries that have snacks or  Build Dynamic SQL in a Stored Procedure After you have read this article, you will get to learn the basics of a dynamic SQL, how to build statements based on variable values, and how to execute constructed statements using sp_executesql and EXECUTE() from within a stored procedure.

Dynamically populate a select field's choices, Description Need to dynamically populate a field's choices? This tutorial will cover how to take a value saved to the options page and use it to override. The following example includes a dynamic query statement with one bind variable (:jobname) and two select columns (ename and sal): stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname'; This example queries for employees with the job description SALESMAN in the job column of the emp table.

Comments
  • "I have tried various ways and none worked" - Well you're going to have to include those. We're not here to write code for you, we're here to help you correct the code you've already written :)
  • Thank you for that... how that i couldn't see?!?
  • Thank you for the response. What will you do if there are like 17 checkboxes? how will the if statements look like?