Dynamic SQL Select: A Flexible Solution for Querying Data with Dynamic Schema

We usually tend to use the wildcard character (*) when we want to select all the columns from a table, but we’re not fully aware that this practice affects our performance and also makes us vulnerable to SQL injection (we talked about SQL injection here) when using the SQL statement in the backend of a web application.

Source: https://www.reddit.com/

We don’t want to become the villains in our own story so to mitigate these issues, I recommend avoiding using the wildcard character(*) by explicitly specifying the column name in your SELECT statement. By doing so, you can improve the performance by retrieving only the required columns and also reduce the risk of SQL injection.

But what we should do if new columns are added to the table?

This is an excellent question…Well, in this case, we’ll turn to an old friend named “Dynamic SQL” (meet our good friend here).

In this article, we’ll discuss a valuable solution to create and execute SELECT statements on the fly with dynamic SQL.

If you’re a person who hates repetitive work (and you’re also a bit lazy like me), you’ll be looking for a solution that saves you from writing all the column names; we all know that repetitive and manual labor is exhausting and, personally, I try to avoid writing column names as much as possible when creating a SQL statement.

So to make my job easier, I created a stored procedure that contains a dynamic SQL select statement that retrieves values from a table by specifying only the schema, table, column name, and the value that will compose the condition based on which rows will be returned.

ALTER proc SP_Dynamic_Select
@schema_name nvarchar(100), @table nvarchar(200), @column_n nvarchar(300),
 @value nvarchar(max)
 as 
    begin
     declare
     @params nvarchar(max) , 
     @condition_test nvarchar(1000),
     @qry nvarchar(max) = N'select ',
     @FirstRow int = 1,
     @key varchar(max), 
     @val varchar(max),
     @type varchar(max),
     @column varchar(max),
     @ErrorMessage nvarchar(4000),
     @ErrorSeverity int,
     @ErrorState int;
     
    begin try 
     if(@schema_name is null or @schema_name = '')
     throw 50020, 'The schema name has not been provided', 1
     else if(@table is null or @table= '')
      throw 50030, 'The table name hasn''t been provided',1
     else
  
     declare db_cursor cursor local for
        select column_name from INFORMATION_SCHEMA.COLUMNS
        where TABLE_NAME = @table and TABLE_SCHEMA = @schema_name
  
     open db_cursor
        fetch next from db_cursor into @column
  
     while @@FETCH_STATUS = 0
      
      begin 
       if @FirstRow = 0
        set @qry= @qry + ', '
        set @qry =@qry + trim(@column)
        set @FirstRow = 0

       fetch next from db_cursor into @column
      end
     
    set @FirstRow = 1 
    close db_cursor
    deallocate db_cursor
  
  set @params= N'@vals nvarchar(1000)'
  set @qry = @qry + ' from '+ quotename(trim(@schema_name)) + '.' 
            + quotename(trim(@table))
  set @qry = @qry + ' where ' + trim(@column_n) + ' = trim(@vals)'
  
   exec sp_executesql @qry, @params, @vals = @value
   --print(@qry)
   end try
    begin catch
       SELECT 
         @ErrorMessage = ERROR_MESSAGE(), 
         @ErrorSeverity = ERROR_SEVERITY(), 
         @ErrorState = ERROR_STATE();
  
       RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
  
     end catch
  
  end

How it was built and how does it work?

1. First of all, we check if the backend developer provided the schema and table name.

   if(@schema_name is null or @schema_name = '')
   throw 50020, 'The schema name has not been provided', 1
   else if(@table is null or @table= '')
    throw 50030, 'The table name hasn''t been provided',1

If either of these two hasn’t been provided, the store procedure will notify you with a message.

2. We started composing the cursor that helps us iterate through the rows of the system table INFORMATION_SCHEMA.COLUMNS in SQL Server

declare db_cursor cursor local for
      select column_name from INFORMATION_SCHEMA.COLUMNS
      where TABLE_NAME = @table and TABLE_SCHEMA = @schema_name

   open db_cursor
      fetch next from db_cursor into @column

It will retrieve the column names based on the schema and table names provided by the backend developer via the stored procedure parameters (@table, @schema_name).

3. Inside the cursor, we’ll add each column name to the variable that will contain the entire SELECT statement and close and deallocate the cursor.

declare
   @qry nvarchar(max) = N'select '

while @@FETCH_STATUS = 0
    
    begin 
     if @FirstRow = 0
      set @qry= @qry + ', '
      set @qry =@qry + trim(@column)
      set @FirstRow = 0
     fetch next from db_cursor into @column
    end

set @FirstRow = 1 
  close db_cursor
  deallocate db_cursor

4. After closing & deallocating the cursor, we complete the SELECT statement by adding “FROM” and “WHERE” clauses (in this case we had to retrieve values from a table that met a condition).

set @qry = @qry + ' from '+ quotename(trim(@schema_name)) + '.' + quotename(trim(@table))
set @qry = @qry + ' where ' + trim(@column_n) + ' = trim(@vals)'

I used the SQL function “trim” to ensure data consistency and correctness and “quotename” to place square brackets around schema and table names to avoid errors when the names contain spaces.

5. Integrate binding parameters to minimize the risk of SQL injection.

exec sp_executesql @qry, @params, @vals = @value

6. Error handling part to ensure the reliability and security of the database.

begin catch
     SELECT 
     @ErrorMessage = ERROR_MESSAGE(), 
     @ErrorSeverity = ERROR_SEVERITY(), 
     @ErrorState = ERROR_STATE();

     RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

  end catch

In conclusion

Dynamic SQL select statements are a powerful tool that can greatly enhance the flexibility and functionality of your database applications. They give you the support you need to create customizable queries that can adapt to changing business requirements, dynamic user input, and varying data schemas.

By understanding how to use them effectively and responsibly, you can take your SQL programming skills to the next level and unlock new possibilities for your applications.

You can find this stored procedure also on my GitHub.

Till next time, do not forget you can find other interesting data-related articles on my Medium.

If you liked my articles, let’s spend our coffee break together here 😉. Thank you for your support!