Dynamic query

Published on: 15th Oct 2013

Updated on: 19th May 2021

Overview

Dynamic query is the query that you composed at runtime and it is not known to the SQL server until you execute it. Usually, the dynamic query is stored in a variable and then passes this variable to sp_executesql (i.e., a system stored procedure) for execution.

How does it work

sp_executesql compiles the query at runtime, creates the execution plan and then executes it.

For example, the following query returns all the object ID-s in the database.

declare @sql nvarchar(max)

set @sql = '
    declare @tb table ( id int )

    insert into @tb (id)
    select object_id from sys.objects

    select * from @tb
'
exec sp_executesql @sql

You may also create a parameterized dynamic query that will look like this,

declare
    @sql nvarchar(max)
    , @sql_param nvarchar(max)

set @sql = '
    select object_id
    from sys.objects
    where object_id < @id
'

set @sql_param = '@id int'

exec sp_executesql
    @sql
    , @sql_param
    , 20

Advantage of parameterized dynamic query

Compiles once and reuses multiple times.

The query execution plan will be cached and reuse it upon next calls. With the auto-caching to the execution plan, the performance of the parameterized dynamic query will be more consistent and predictable.
It avoids SQL injection.

If you are composing the dynamic query by concatenating the user parameter value, it has a higher chance for the user to find a way to break in.

For example, in the following query, where @user_param is coming straight from the user.
```
  set @user_param = 'ABC Supplies'
  set @sql = 'select * from tb_sales '
  set @sql = @sql + ' where customer = ''' + @user_param + ''''
```
If the user passed in something like below,
```
  set @user_param = 'ABC Supplies''; drop table tb_sales;declare @a char(1)='''
```
Then, the dynamic query will look like this,
```
  select * from tb_sales  where customer = 'ABC Supplies'; drop table tb_sales;declare @a char(1)=''
```
As a result, tb_sales table will be dropped.

Disadvantage of using dynamic query

The query generated at runtime based on the flag turned on or off by the user is a difficult test and not intuitive.

For example, the daily sales report uses dynamic query

  create proc pr_rpt_daily_sales (
      @start_dt datetime
      , @end_dt datetime
      , @include_dept int
  )
  as
  begin
      declare
          @sql nvarchar(max)
          , @sql_param nvarchar(max)
          , @user_param nvarchar(max)

      set @sql = 'select '
      if @include_dept = 1
          set @sql = @sql + ' d.dept_desc, '
      set @sql = @sql + ' s.trans_date, total_sales = sum(s.qty * s.price) from tb_sales s '
      if @include_dept = 1
          set @sql = @sql + ' inner join tb_dept d on d.dept_id = s.dept_id '
      set @sql = @sql + ' where s.trans_date between @start_dt and @end_dt '
      set @sql = @sql + ' group by '
      if @include_dept = 1
          set @sql = @sql + ' d.dept_desc, '
      set @sql = @sql + ' s.trans_date '
      set @sql = @sql + ' order by '
      if @include_dept = 1    
          set @sql = @sql + ' d.dept_desc, '
      set @sql = @sql + ' s.trans_date'

      set @sql_param = '@start_dt datetime, @end_dt datetime'
      exec sp_executesql
          @sql
          , @sql_param
          , @start_dt
          , @end_dt
  end
  go

For user A wants to find out the daily sales for January, he will execute the following stored procedure,

  exec pr_rpt_daily_sales
      @start_dt = '2021-01-01'
      , @end_dt = '2021-01-31'
      , @include_dept = 0

And the actual query will look like this:

  select
      s.trans_date
      , total_sales = sum(s.qty * s.price)
  from tb_sales s
  where   
      s.trans_date between @start_dt and @end_dt
  group by s.trans_date
  order by s.trans_date

For user B who wants to find out the daily sales by department for January, he will execute the following stored procedure,

  exec pr_rpt_daily_sales
      @start_dt = '2021-01-01'
      , @end_dt = '2021-01-31'
      , @include_dept = 1         --<<===change this flag to include department

And the actual query will look like this:

  select
      d.dept_desc
      , s.trans_date
      , total_sales = sum(s.qty * s.price)
  from tb_sales s
  inner join tb_dept d on d.dept_id = s.dept_id
  where   
      s.trans_date between @start_dt and @end_dt
  group by
      d.dept_desc
      , s.trans_date
  order by
      d.dept_desc
      , s.trans_date

Provides advanced feature for your application

We are using dynamic query mainly in the following area,

Provides paginated record. For example, there are 1,000 bills and the user wants to see the first 50 bills and then the next 50 bills, on and on.
Reporting process. For the statistical report, you need to write lots of dynamic queries because of the user requirements (see the following section).
Provides a flexible search feature for the user. We used to have a search screen that comes with different fields. Users will have to key in their search text into the field that they want and hit the search button.

Nowadays, users are asking for matching some text with the pre-selected fields. In this case, dynamic query will be able to fulfill this requirement.

With the help of dynamic query, you can develop a more powerful application.

What are the most common user requirements for the report?

Lots of criteria and allows different combinations.
The final result is a subset of a result.
Allows choosing different sorting orders.
Allows choosing different groupings.
The fields in the report were specified by the user at runtime.

With dynamic query, you will be able to compose the dynamic query that meets all the above requirements.

What is the catch of using dynamic query

Dynamic query seems like able to help in fulfilling the user requirements but it also causes an issue: slow query performance.

You need lots of data to test your query, fine tuning the query and a better index.

From our experience, we were facing a slow performance issue. It was difficult to identify what caused it because the query most of the time was fast and sometimes was slow. After extensive testing and research, we found out that parameter sniffing was the cause. The query hint below solved the instability performance,

select *
from tb_sales
where
    customer = @customer
option (optimize for unknown)   --<<==== try this

Note: this query hint will not solve all performance issues. In case you are facing any strange performance issue, you will have to first find out the cause and then look for the appropriate solution.

In case you want to find out more about this, you will have to search this topic on the Internet: "parameter sniffing".

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.