Dynamic SQL

Dynamic SQL statements are SQL statements that are constructed dynamically from a calling program.

For example let us take the case of a program in Visual Basic that has an ADO connection to a MS-ACCESS database.

The ADO connection object in turn uses a SQL execute statement, which takes a SQL statement as one of its inputs and holds a record status variable to indicate if the query has returned a not found or has returned duplicate records. Usually the SQL statement would be one that serves a specific purpose such as displaying a list of employee names.

Select Emp_Name from Emp_Master.

In addition to the above query suppose one has a form field that takes Emp_No and Dept_NO as inputs to the form and passes it to the calling program. Now we want to select an employee with a specific employee number or an employee with a specific department number.

So we need a query that is dynamic and which pertains to either a specific employee number or department number.

So the same query used above will appear as

Select Emp_Name from Emp_Master where emp_id = "'&Emp_No&"'"

Here the Emp_No is passed from the calling program and is concatenated to the main query dynamically using "'&Emp_No&"'"

The query will appear as

Select Emp_Name from Emp_Master where emp_id = 'E001' or in the case where 'E004' is passed from the main form the query will dynamically change to Select Emp_Name from Emp_Master where emp_id = 'E004'.

In the case when department number is passed from the main program the query will be coded as

Select Emp_Name from Emp_Master where dept_name = & "'" &dept_name&"'" where & is the concatenation operator.

So dynamically if D002 is passed as a variable from the main program the query will appear as Select Emp_Name from Emp_Master where dept_name = 'D002'.

The ADO.Execute statement (Used for executing a SQL statement within an ADO connection will appear as ADO.Execute("Select Emp_Name from Emp_Master where dept_name = 'D002'), earlier the ADO.Execute would contain only Static SQL statements. Here there may be more methods other than ADO used to connect an external program to the database

Using dynamic SQL one can code where clauses, SQL statements, pass table names. The only disadvantage of using dynamic SQL is that the statements are not compiled prior to run time which may cause performance degradation..

Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan's blog at http://www.sqlfood.com/ where he has shared more details on this.