Dynamic SQL Statements

In database queries in server-side scripting languages such as Active Server Pages or Cold Fusion, one can execute a SQL statement against a database.  Often the SQL statement to be executed is determined dynamically, such as when a URL parameter is passed from a link or a form in another page and the URL parameter is incorporated into the SQL statement.  This is typically done in the WHERE clause.  For example, in ASP (using JavaScript) one might write:   

str = "SELECT * from students WHERE sid = " + Request.QueryString("sid");
rsItem = connCW.Execute(str);

The Request.QueryString() method returns the value associated with the sid name value pair in the URL so that the SQL statement that is actually executed becomes something of the form

SELECT * from students WHERE sid = 3

However, there are other ways in which dynamic SQL statements can be formed.  Consider a case where one is inserting data into one of three tables and the choice of table is made in radio buttons named "choosetable" on  the page that requested the page that does the insertion.  The radio buttons on the previous page will be as follows:

<input type="radio" name="choosetable" value="events">
<input type="radio" name="choosetable" value="resources">
<input type="radio" name="choosetable" value="training">

Whichever radio button is selected will generate a name value pair choosetable=bill where bill is the name of the table. Request.Querystring("choosetable") will return a value of bill 

Assume that the field names where data is to be inserted do not vary among the three tables.  There are several ways to form the SQL statement:  The first method is the best because the code on this page would not need to be changed if the table names were changed or if the number of tables would be increased.

Method 1 (preferred): Form your SQL statement where the name of the table is supplied directly by the form 

sep="', '";
str = "INSERT INTO " + Request.QueryString("choosetable") + " (title, description) VALUES ('"
str += Request.QueryString ("title") + sep + Request.QueryString ("description") +"')";
dbConnection.Execute(str);

Method 2: Use if statements based on the value of Request.QueryString ("choosetable") to only execute one of your Execute commands.  Remember in JavaScript that the test for equality is a double equal sign and parentheses are required to surround the condition being tested and the statement to be executed if the condition is true follows the condition.  If a statement block is to be executed if the condition is true, then the statement block must be surrounded by curly braces..

stra = "INSERT INTO events (title, description) VALUES etc..
strb = "INSERT INTO resources (title, description) VALUES etc.
strc = "INSERT INTO training (title, description) VALUES etc.
if (Request.QueryString("choosetable")=="events") dbConnection.Execute(stra);
if (Request.QueryString("choosetable")=="resources") dbConnection.Execute(strb);
if (Request.QueryString("choosetable")=="training") dbConnection.Execute(strc);

Method 3: Use a single Execute() command and the variable passed to it has been assigned the value the appropriate value based on if statements based on the value of Request.QueryString("choosetable")

if (Request.QueryString("choosetable")=="events") str = "INSERT INTO events (title, description) VALUES etc..
if (Request.QueryString("choosetable")=="events") str = "INSERT INTO resources (title, description) VALUES etc.
if (Request.QueryString("choosetable")=="training) str = "INSERT INTO training (title, description) VALUES etc.
dbConnection.Execute(str);

Revised: October 4, 2002