Dynamic SQL usage

One of the usefulness about dynamic SQL is for database maintenance. For example, you want to run the maintenance process in the selected databases.

Below is the flow of using dynamic SQL to ease the maintenance:

• Create a table that stores the database names for your system.
• Declare a cursor and loop through all your database names and match the process number (explain later).
• Generate the dynamic SQL statement to be executed against the selected databases.
• Finally, execute the dynamic SQL.

The reason to have a table to stores your database names:

• You are not interested in all databases stored in sys.databases. You just want to run the script against the selected databases.
• The database name did not follow any convention before you take over the maintenance. In you are handling the database server, then, you should follow a standard naming convention for the database name. This allows you to query the database name from sys.databases.
• You don't want to execute the SQL statement against all your application databases. Only certain databases are going to run script 1 and other databases are going to run script 2, etc.

The table design to stores the database name:

• db_name nvarchar(255) - this is the database name
• process_1 int - if '1' means run the maintenance stored procedure #1. '0' to ignore.
• process_2 int - if '1' means run the maintenance stored procedure #2.'0' to ignore.
• (other process flag).

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.

Generating "reminder" for the user

In a mid to large scale application, often you need to run scheduled tasks which can be schedule through the SQL Agent. One type of the schedule is to generate reminder or alert for the user. For example, inform the collection department about the overdue invoice.

Below is the strategy to implement this type of process:

1. Create a stored procedures to scan the data.
2. In this stored procedure, it contains the business logic about what data will meet the requirement and then generate the reminder record. 
3. The reminder record will be saved into tb_mail (this table stores the email to be pickup by a C# program).
4. Develop a program in the C# application which will send all the emails in tb_mail table.

In case you don't want to notify the user through email, you may append this reminder record into an "alert" table (which can be "tb_log" table in our design). Then, display the alerts upon user login.

For those alert requires immediate attention, you may send the reminder or alert through SMS (short messaging).

Benefits of using stored procedure instead of writing codes in C#

Started from my favorite programming language, Visual Basic 6 (that was in 1997), I've been thinking where is the best place to implement the "business rule". After many years of moving the business rules from the the programming language to the database and vice versa, I've finally decided the best place for the business rule is in the stored procedure.

The simplest stored procedure is for SELECT, INSERT, UPDATE and DELETE. Indeed, you can do more than that. Benefits of using the stored procedure:

• Create audit log when doing any of the CRUD operation.
• Able to implement sophisticated control in what kind of data can be return to caller.
• Easier to enhance/process the records before returning the records.
• Stored procedure has been compiled and it runs faster than the ad-hoc query.
• Able to enhance the stored procedure at anytime.
• You don't have to recompile the application if there are any changes in the rule.
• You don't have to redistribute the application (at client workstation or web server) if there are any changes in the rule.

Well, I know that you might not 100% agree with my argument. But, as a customize solution builder like me, we can't ask all the users to stop for a while and wait for the latest update.