Dynamic Generator is a simple Windows application to generate dynamic SQL Stored Procedures on the fly, easily and error free; just enter the server name with authentication, select the database, point to the table to work with, press ‘Generate’, and it’s done.
Very often, when I’m asked to develop a data driven application or a CMS, I’m asked to implement an “Advanced Search” feature; this can be done using Dynamic SQL.
Although a more efficient way will be using the COALESCE function, dynamic SQL is the solution for doing “All words”, “Any word”, and “Exact Match” string searches.
Writing these statements can be tiresome, note that (unlike usual Stored Procedures) you may need more than one variable for the same field, .i.e., if it’s a range field (date or money), you will need two variables, from and to; if it’s a string variable (
nvarchar, etc.), you will need five variables: four words the user entered, and a flag to check which search type was selected (“All words”, “Any word”, or “Exact Match”).
How the Code Works
Dictionary<string, string> dict_Fields is a dictionary holding the field name as a key, and the field type as value. When the user selects a table, we have to get all the fields within it.
private void lstbx_Tables_SelectedIndexChanged(object sender, EventArgs e)
if (lstbx_Tables.SelectedIndex < 0)
dict_Fields = new Dictionary<string, string>();
using (SqlConnection conn = new SqlConnection(connstring))
string strCommand = @"SELECT COLUMN_NAME, DATA_TYPE FROM " +
@"INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '" +
lstbx_Tables.SelectedItem.ToString() + "'";
SqlCommand cmd = new SqlCommand(strCommand, conn);
SqlDataReader dr = cmd.ExecuteReader();
cmb_Fields.SelectedIndex = 0;
Now all we have to do is to iterate through this dictionary, checking the field type.
If it’s a
string field, we will append an integer variable, with the field’s name plus the word “
Type”, and four
nvarchar variables with the field’s name plus the variable index, with size 15 characters (all initialized to
Example: If we have a field called
CategoryName. Then, we have the following variables created:
@CategoryNameType int = NULL, @CategoryName1 nvarchar(15) = NULL,
@CategoryName2 nvarchar(15) = NULL,
@CategoryName3 nvarchar(15) = NULL,
@CategoryName4 nvarchar(15) = NULL
If it’s a range field (money, date-time etc.), we will append two variables of the same type, from and to. E.g.,: if we have a field called
HireDate, we have the following variables created:
@HireDateFrom datetime = NULL,
@HireDateTo datetime = NULL
If it’s a string field, append an ‘
if’ statement to check the search type, then four other statements for the variables we just created.
if @CategoryNameType = 0 begin
if @CategoryName1 is not null
SELECT @sql = @sql + N' and CategoryName LIKE @CategoryName1$'
if @CategoryName2 is not null
SELECT @sql = @sql + N' and CategoryName LIKE @CategoryName2$'
if @CategoryName3 is not null
SELECT @sql = @sql + N' and CategoryName LIKE @CategoryName3$'
if @CategoryName4 is not null
SELECT @sql = @sql + N' and CategoryName LIKE @CategoryName4$'
The same goes for ‘Any word’ and ‘Exact match’.
If it’s a range field, append two
if statements. Any other field will append just one statement.
That’s it, happy search.
- 10th November, 2009: Initial post
- 19th November, 2009: Updated article
- 13th June, 2010: Updated article