Generate a series of number

Below is a way to generate a series of number (10 records where the value starts from 1 to 10) with Common Table Expression (CTE):

with tb (ii)
as (
    select
        1 ii
    union all
        select ii + 1
        from tb
        where ii + 1 <= 10
)

select *
from tb

For the detailed explanation, check this out:

http://www.databasejournal.com/features/mssql/article.php/3502676/Common-Table-Expressions-CTE-on-SQL-2005.htm

When to use it

• Use CTE + UNION ALL to generate hierarchical result. For example, you have a discussion forum where it allows the members to post question (a thread) with many replies (many level of sub-threads).
• Reduce re-typing the same SELECT statement especially you are aggregating the data.

Reading the system settings using stored procedure/function

Stop hard-coding the constants in a stored procedure. Instead, you should read the value from your "settings" table or returning the constant value by a function call. In C# and other programming languages, you can declare constant in a class and use it anywhere in the program. But, in MSSQL, you can't do that.

To get the constant or settings, you may try to do this:

• Create a function which returns the value (could be constant or read a field from a table).
• Create a stored procedure and returns the value through OUTPUT parameter.

There are some factors which you might have to decide how to design the stored procedure/function:

• Should the value configurable by the the user? If no, you may return the value by a function call. If the value is user configurable, then, you need a "setting table" to store the value.
• If the value must be stored in a table, do you store the value in a generic table structure (with prop_name and prop_value fields) OR do you need to design a "settings" table which contains only 1 record (with the appropriate field name which can reflect the setting)?

In our database design, we have a generic table that store setting. In case the values are frequently use, we will design a setting table to store the user configured values.

Show me the runtime value

In MSSQL, you may use PRINT statement to display some information when you are executing stored procedure or a SQL batch. This is very helpful to debug the stored procedure which contains many IF statements or you would like to find out the runtime value of certain variable.

For example,

declare @cust_name nvarchar(255)

select @cust_name = cust_name
from tb_customer
where cust_code = 'A001'

print @cust_name

When to use it:

• Your stored procedure contains many IF statement and you want to know which path has been executed with different parameter value.
• You want to find out which stored procedure has been called when the main stored procedure has been executed.
• You want to find out what is the runtime value of the variables.
• You want to find out where is the process has exited.

Enabled debugging mode within the stored procedure without using parameter

In our previous article, we discussed that the stored procedure should contain a parameter call "@debug_mode" so that it can be debug in the development phase. But, some people might feel that it makes the stored procedure looks ugly. In this article, we are going to show you another way to enable the debugging in the development without having this parameter.

To store the information to the current session/connection, use the following command:

   SET CONTEXT_INFO 0x1000

To retrieve the value, use the following function:

   SELECT context_info()

context_info able to store upto 128 bytes of information. The only thing is that you must learn how to work with bitwise operator.

First thing you have to do with context_info is to define the meaning of each "box". For example, you need to implement 4 hidden features. Then, the value will look like "0x0000" where "0x" indicates it is hex value and you can't use these two character to store any information. But, the 4 digits after "0x", you are freely to define the meaning.

• 1st box - if "1" means enable the debugging mode. Otherwise, "0".
• 2nd box - could be defined as "1" to log down the process details.
• 3rd box - could be defined as "1" to print the runtime value in the IDE.
• 4th box - could be defined as "1" to rollback the process so that it won't disturb the test data.

Lastly, before you run your stored procedure, you have to call "set context_info 0x1001" to enable the debug mode and rollback the changes.

Some modification is required in your stored procedure:

declare
  @info varbinary(128),
  @debug_mode int,
  @rollback_data int

select @info = context_info()

if @info & 0x1000 = 0x1000
  select @debug_mode = 1
else
  select @debug_mode = 0

if @info & 0x0001 = 0x0001
  select @rollback_data = 1
else
  select @rollback_data = 0

(..contine your process here..)

References:
http://msdn.microsoft.com/en-us/library/ms187768.aspx
http://msdn.microsoft.com/en-us/library/ms176122.aspx