Executing dynamic SQL

In MSSQL, it is able to execute dynamic SQL. Meaning, you are allowed to execute the SQL statement that stores in a variable by calling sp_executesql system stored procedure. It compiles the SQL statement at runtime and then execute it. You may also create parameterized SQL statement so that it compiles once and run multiple times (for example, inserting many records within a loop).

Whenever it's possible, try to avoid using the dynamic SQL. This is because each time you are executing the dynamic SQL statement (not within a loop in the stored procedure), it will be recompiled and increase the execution time.

The advantage of implementing this idea will save you from having a permanent table to store the reporting data  or data for the current session. Also, it saves you having tuning the SQL statement with complex WHERE clause where some of the conditions were optional.

The following sample code returns all the object ID-s in the database by using the dynamic SQL statement.

declare @sql nvarchar(max)

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

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

    select * from @tb
'
exec sp_executesql @sql

I will discuss a bit more in detail in my next article.