MSSQL Tips & Tricks

We are moving

2021-05-14T12:43:00.003+08:00

We are moving to our new blog site:

The decision table for discount process

2021-01-12T18:03:00.001+08:00

Overview

In the context of Point of sales (POS) for many industry, it is quite common that a shop might run promotion on different period, weekday and whether holiday should give discount or not. The discount process that we are discussing in this article is an automated process. Meaning that the cashier will scan the barcode of the item and the discount will be given automatically.

To run a complex price discount process based on the date factor, the decision table will look like this:

Rule #	Is holiday	Day of week	Exclude holiday	Include holidy	Give discount?
1	n	y	n	n	y
2	n	n	n	n	n
3	y	y	n	n	y
4	y	y	y	n	n
5	y	y	n	y	y
6	y	n	n	n	n
7	y	n	y	n	n
8	y	n	n	y	y

The decision table explanations

'Is holiday' is a table that stores the date of holiday. This table is to be setup by the system admin.
'Day of week', 'exclude holiday' and 'include holiday' is a boolean (checkbox field on the scren) and the setting should be kept in the 'price discount' table. This table is to be setup by the system admin as well.
'Day of week' which has 7 fields that includes Monday, Tuesday, etc. 'y', means that week day has discount.
'Exclude holiday' is a field that is used in conjunction with 'day of week'.
'Include holiday' is a field that is used in conjunction with 'day of week' and it is mutually exclusive from 'exclude holiday'.

Use case

Discount is applicable for weekdays (Monday to Friday) regardless holiday - rule #1 and #3 applies.
Discount is applicable for weekdays (Monday to Friday) excluding holiday - rule #4 applies.
Discount is applicable for weekdays (Monday to Friday) and applicable holiday - rule #5 applies.
Discount is not applicable for weekdays (Monday to Friday) regardless holiday- rule #2 and #6 applies.
Discount is not applicable for weekdays (Monday to Friday) and applicable on holiday - rule #8 applies.
Rule #7 is invalid. So, we may ingore it.

Table structure

We need three tables to store the master setup. One for the holiday, one for the product and another one for the discount. It's a one to many relationship between tb_prod and tb_discount.

create table tb_holiday (
    dt datetime not null
);

create table tb_prod (
    prod_id int not null
    , price money not null
);

create table tb_discount (
    prod_id int not null        
    , start_dt datetime null
    , end_dt datetime null
    , discount_pct numeric(6,2) null
    , for_mon int null
    , for_tue int null
    , for_wed int null
    , for_thu int null
    , for_fri int null
    , for_sat int null
    , for_sun int null
    , exclude_holiday int null
    , include_holiday int null
)

Setting up the test data

delete from tb_holiday;
delete from tb_prod;
delete from tb_discount;

insert into tb_holiday (dt) values 
('2021-01-13');

insert into tb_prod (prod_id, price) values
(1, 100)
, (2, 200);

insert into tb_discount (prod_id, start_dt, end_dt, discount_pct
, for_mon, for_tue, for_wed, for_thu, for_fri, for_sat, for_sun
, exclude_holiday, include_holiday) values 
(1, '2021-01-01', '2021-01-31', 0.1
    , 1, 1, 1, 1, 1, 0, 0
    , 1, 0)
, (1, '2021-02-01', '2021-02-28', 0.15
    , 1, 1, 1, 1, 1, 0, 0
    , 1, 0)
, (1, '2021-01-01', '2021-01-31', 0.05
    , 0, 0, 0, 0, 0, 1, 1
    , 0, 1)
, (1, '2021-01-01', '2021-01-31', 0.03
	, 0, 0, 0, 0, 0, 1, 1
	, 0, 1);

To query the discount for any product

declare
    @is_holiday int 
    , @dow int 
    , @curr_dt datetime 
    , @prod_id int

set @prod_id = 1
set @curr_dt = '2021-01-11'
set @dow = datepart(weekday, @curr_dt)

if exists(
    select *	
    from tb_holiday
    where dt = @curr_dt
)
begin
    set @is_holiday = 1
end
else
begin
    set @is_holiday = 0
end

select 
    discount_pct
from tb_discount d
where
    prod_id = @prod_id
    and @curr_dt between d.start_dt and d.end_dt
    and (			
        (@is_holiday = 1 and (d.include_holiday = 1 and d.exclude_holiday = 0))
        or ((
                   (@dow = 1 and d.for_sun = 1)
                or (@dow = 2 and d.for_mon = 1)
                or (@dow = 3 and d.for_tue = 1)
                or (@dow = 4 and d.for_wed = 1)
                or (@dow = 5 and d.for_thu = 1)
                or (@dow = 6 and d.for_fri = 1)
                or (@dow = 7 and d.for_sat = 1)
            ) 
            and (
                (@is_holiday = 0)
                or (@is_holiday = 1 
                    and (d.include_holiday = 1 or d.exclude_holiday = 0)
                )
        ))
    )

The above query first is to identify the day of week and check to see if it is holiday.
Follow by filtering the discount record by prod ID and the period.
It then checks the day of week and holiday.
Finally, returns the discount percentage.
Test case (please change the value of @curr_dt in the query)
- For @curr_dt = 2021-01-11 (Monday), the discount percentage is 10%. The week day discount in Jan 2021.
- For @curr_dt = 2021-01-13 (Wednesday, holiday), the discount percentage is 3%.
- For @curr_dt = 2021-01-16 (Saturday), the discount percentage is 5%. There is a caveat in the result because the query returns two discount records.
- For @curr_dt = 2021-02-11 (Thursday), the discount percentage is 15%. The week day discount in Feb 2021.
Caveat - there might be more than one discount record returned by the query. To avoid this from happening,
1. During the discount setup time, you may have to prompt the user on the conflict OR
2. In case the conflicting discount allows to be saved, you may have to return the latest discount record using TOP 1 + ORDER BY modified_on or any other condition that will force the query returning only one discount record.

Disabling auto-close in the database

2019-11-28T16:23:00.001+08:00

If auto-close has been enabled in the database, it will have negative effects on the hard disk spinning load and also might flood the event log with "starting database..". It has been widely discussed in the Internet why it's not a good idea to enable it.

Anyway, here is the SQL script to disabled auto-close feature.

set nocount on

declare
    @seq int
    , @db sysname
    , @sql nvarchar(max)

select seq = row_number() over (order by name), name
into #db_list
from sys.databases
where is_auto_close_on = 1

set @seq = 0

while exists(
    select *
    from #db_list
    where seq > @seq
)
begin

    select top 1
        @seq = seq
        , @db = name
    from #db_list
    where seq > @seq
    order by seq

    print 'reseting auto close for ' + @db

    set @sql = 'alter database ' + @db + ' set AUTO_CLOSE off'

    ----print @sql
    exec sp_executesql @sql

end

print 'done'

drop table #db_list

set nocount off

Loop all records

2019-10-18T16:46:00.002+08:00

Basically, there is another way of looping which uses CURSOR. But, it requires record locking which may not work as fast as you think.

In this article, we are going to use a temporary table to keep the data and then loop it with "seq" filter. The following example which is printing all table names on the screen. You may modify the PRINT line so that it compose the necessary SQL statement before you print it or execute any dynamic SQL.

set nocount on

declare
   @seq int
   , @name nvarchar(255)

-- declare a temporary table in the memory.
declare @tb table (
   seq int identity(1,1)
   , name nvarchar(255)
)

--copy all the necessary records to the temporary table '@tb'
insert into @tb (name)
select name
from sys.objects
where
   type = 'u'and
   name like 'tb_suggest%'
order by name

-- init the var before use
set @seq = 0

-- start the lopp
while exists(
   select *
   from @tb
   where seq > @seq
)
begin

   -- always load the first record that is greater than
   -- last seq.
   select top 1               --<<=== this is very important!!!
       @seq = seq
       , @name = name
   from @tb
   where seq > @seq
   order by seq               --<<=== this is very important!!!

   -- do whatevery necessary process here.
   -- you may use 'set xxx' to join the sql string before print it out.

   print '@name => ' + @name

end

How to find out which index is missing

2019-09-19T11:37:00.000+08:00

To find out the missing index, we have to analyze the information in the following system views:

sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details

You may download the script that combine all the necessary information from the following URL:

   https://gist.github.com/alexsorokoletov/a079629f9e1435c7f81f

And here is the SQL script:

SELECT
    CONVERT (varchar, getdate(), 126) AS runtime,
    mig.index_group_handle, mid.index_handle,
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,

    'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
        + ' ON ' + mid.statement
        + ' (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
        + ')'
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

    migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
--and database_id = DB_ID('my_database')

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

How to use it?

I'm relying on improvement_measure value and I review the top 5 missing index information and then decide if I should create an index.
We should not create all the indexes returned by this query. Because some of the missing indexes can be merge into one index.
We should review the existing indexes and compare against what is missing and then decide the new index. This might involves deleting the existing index before creating a new one.

Life is tough with naming convention especially we want to know how many times that we have reviewed a particular index. My naming rule works this way:

IX_my_table_1 - this is the first index.
IX_my_table_2 - this is another index.
IX_my_table_2_1 - this is the newer version of index where IX_my_table_2 has been dropped and merge with the new missing columns.

Fixing the database state

2019-09-14T11:13:00.001+08:00

Sometimes after rebooting the server, the database state might stick in "recovery". Waiting and waiting and rebooting might not change to the normal state.

In this case, we need to fix this issue manually.

To view the current database state:

   SELECT name, state_desc from sys.databases

To fix the problematic database:

   ALTER DATABASE test SET EMERGENCY;
   GO

   ALTER DATABASE test SET SINGLE_USER
   GO

   DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
   GO

   ALTER DATABASE test SET MULTI_USER
   GO

Before running the above commands, please make sure you have done sufficient research on the Internet before executing it!

Running the same stored procedure sequentially

2018-10-26T00:40:00.001+08:00

Use the following system stored procedure to block the same stored procedure from running concurrently.

sp_getapplock

Finally, you must call the following so that next process is allowed to run the same stored procedure.

sp_releaseapplock

Note:

The above system stored procedures must be running within a database transaction.
If @@lock_timeout is -1, then, sp_getapplock will be block until it has been released and commit/rollback must be called.
If @@lock_timeout is not -1, then, the result will be less than zero (failed). But, sometimes it returns "> 0" (i.e., successfully get he app lock) and on the other hand it might fail to lock the "record in the table". In this case, error #1222 will be raised by SQL server.

Getting the deadlock details

2018-10-15T10:03:00.001+08:00

In SQL 2012, you may query the deadlock details with the following SQL statement:

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS XEvent
       FROM ( SELECT CAST(target_data AS XML) AS TargetData
              FROM sys.dm_xe_session_targets st
                   JOIN sys.dm_xe_sessions s
                   ON s.address = st.event_session_address
              WHERE s.name = 'system_health'
                    AND st.target_name = 'ring_buffer'
              ) AS Data
              CROSS APPLY
                 TargetData.nodes
                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent )
      ) AS src;

The following query is for SQL2008:

SELECT CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML
                      SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     name = 'system_health'
                                AND target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                                     event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS tab ( event_data )

For more details about deadlock and resolution, please read the following article:

https://www.red-gate.com/simple-talk/sql/database-administration/handling-deadlocks-in-sql-server/
https://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-deadlock

Something about deadlock

2016-12-22T14:51:00.003+08:00

This is a nice article that talks about how to find more details about deadlock.

Finding and Extracting deadlock information using Extended Events

http://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx

Using TRY/CATCH to handle the deadlock

https://technet.microsoft.com/en-us/library/aa175791(v=sql.80).aspx

Wait for a while..

2016-12-22T11:44:00.000+08:00

Here is the code that you need to put your process into sleep mode:

declare @wait_time nvarchar(255)

print 'start working on it.. => current ms: ' + cast(datepart(millisecond, getdate()) as nvarchar)

set @wait_time = '00:00:00.' + left(cast(cast(rand() * 1000 as int) as nvarchar), 3)
waitfor delay @wait_time

print 'im done and waited for ' + @wait_time
+ ' => current ms: '
+ cast(datepart(millisecond, getdate()) as nvarchar)

Concatenating the records and use appropriate separator

2015-06-23T11:41:00.001+08:00

If we have 4 records, we want to concatenate all 4 records and the last separator should be "&" symbol. We can achieve this by using row_number() function and then decide whether the separator is "," or "&".

For example:

declare @s nvarchar(max)

create table #tb1 (
    email nvarchar(255)
)
insert into #tb1 values ('a@a.com');
insert into #tb1 values ('b@a.com');
insert into #tb1 values ('c@a.com');
insert into #tb1 values ('d@a.com');

select
    @s = coalesce(@s
        + case when rowidx < cnt then ',' else ' & ' end
        + email, email)
from (
    select
        rowidx=row_number() over( order by email )
        , email
        , cnt = (select count(*) from tb1)
    from #tb1
) as a

select @s

drop table #tb1

Code sharing

2013-11-25T15:43:00.001+08:00

I've been thinking of sharing my codes for many years and finally I decided to do it.

I have uploaded the table structures, views, stored procedures and functions into Github. You may download and use it freely. I will update the contents if I encounter the useful script and table design.

https://github.com/lauhw/MSSQLProc

Let me know if you have anything to share with me. ;)

Get the Guid.Empty constant in MS SQL

2013-08-20T10:42:00.000+08:00

The following function returns the Guid.Empty (.Net constant) in MS SQL:

create function dbo.fn_empty_guid ()
returns uniqueidentifier
as
begin
    declare
        @result uniqueidentifier

    set @result = cast(cast(0 as varbinary) as uniqueidentifier)

    return @result

end
go

Usage:

select dbo.fn_empty_guid()

Lock or no lock

2013-04-25T22:24:00.001+08:00

While I was reviewing other programmer's work, I found out that most of the SELECT statements contain this keyword - "WITH (NOLOCK)". So, I posted this question to the programmer who is handling the job. His answer is quite simple, "we need this to reduce the query response time and then our program will run faster". And the actual fact is that, the programmer does not know the side effect of this keyword.

Yes, the query will run faster because it does not require to lock all the necessary records before reading it from the table. But, the side effects is that the data returned by the query might be "dirty" (i.e., the changes have not been committed).

If the program has read the data which have been rolled back by other user, it could lead to many unpredictable situation. This could lead to unsolvable mystery.

Concatenate all records into one line text

2013-02-21T14:07:00.002+08:00

Below is the code that you can use for concatenating all records into one line text:

declare @s nvarchar(max)

select
@s = coalesce(@s + ',' + prod_code, prod_code)
from prod

select @s

Using dynamic SQL for reporting process

2013-01-11T22:31:00.001+08:00

What is the user requirements for the report?

Lots of criteria on the screen but most of them are optional.
The final result is a subset of a result.
User allows to choose different sorting order.
User allows to choose different grouping.
The fields in the report was specified by the user at runtime.

By using dynamic SQL, you will be able to compose the appropriate SQL statement and then meets the user expectation. Another thing is that, you don't need to have many static SQL statements in the stored procedure.

Dynamic SQL usage

2012-10-22T09:00:00.000+08:00

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

2012-10-15T09:00:00.000+08:00

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

2012-10-08T09:00:00.000+08:00

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:

Create a stored procedures to scan the data.
In this stored procedure, it contains the business logic about what data will meet the requirement and then generate the reminder record.
The reminder record will be saved into tb_mail (this table stores the email to be pickup by a C# program).
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#

2012-10-01T09:00:00.000+08:00

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.

Generate a series of number

2012-09-27T12:18:00.000+08:00

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

2012-09-20T00:14:00.001+08:00

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

2012-09-10T09:00:00.000+08:00

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

2012-09-03T09:00:00.000+08:00

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

Allow to run the process again?

2012-08-28T11:52:00.000+08:00

For the stored procedure that is executed by the SQL Agent (i.e., the scheduler), you might have to prevent the same stored procedure run more than once.

Allow re-entrant

Not all processes allow to run more than once. For example, the stored procedure that is responsible for generating monthly statement. Another example is the stored procedure that is generating the exception report.

Why re-entrant

In the development phase, you might want to run the stored procedure as many time as possible so that you can fine tune and debug the process. But, once these stored procedures go live, it will run once for the day, week, month or whatever predefined date.

Now, how to prevent re-entrant
In order to prevent the stored procedure to run more than once for a day, you need to have a condition to check the log table. In our example, the "tb_whatever_process_log" table is keeping track the process date.

    if exists(
        select *
        from tb_whatever_process_log
        where
            process_date = cast(convert(nvarchar, getdate(), 102) as datetime)
    )
    begin
        print 'Already done. Exit'

        set nocount off
        return
    end

    (... continue with the actual process..)

How to test this stored procedure?

Due to the re-entrant prevention condition, you will find it almost like no way to test out the stored procedure during the development phase. An easiest way to do this is to add a new parameter to the stored procedure call "@debug_mode int = 0". In the development phase, you will have to specify the "@debug_mode = 1".