Session variables

  • 21 January 2019
  • 0 replies
Session variables
Userlevel 6
Badge +9

Many applications we develop with the Thinkwise Platform work with so-called administrations, for example because the organization has several subsidiaries, each with their own stock, orders, invoices and purchases, all in one end product database. Users work in a single administration at a time and only see the data for that administration. This is often realized by recording the administration id for each user in the end product.

But what if a user needs to work in multiple IAM applications with different administrations? To solve this problem, we have added support for session variables. Requires SQL Server 2016 or higher!

Session variables

With session variables you can store additional information for a specific user interface of IAM application, that you can use in your business logic.

Session variables can be added by creating ini parameters or extended properties that start with session. , followed by the name of the session variable. For example:

Adding a session variable

Indicium and the Windows and Web user interfaces apply these session variables to the SESSION_CONTEXT of SQL server when setting up a connection.

You can then use the value of a session variable in your SQL business logic or prefilters using the Session_context function:

select SESSION_CONTEXT(N'administration_id');


select * from sales_order where administration_id = 
convert(int, SESSION_CONTEXT(N'administration_id'))


In addition to the self-defined session variables, a session ID is always added to the session_context, called Guid. This guid is generated by Indicium or the user interfaces and remains valid as long as the application is active.

select SESSION_CONTEXT(N'guid') 

--Result: '7DBFC3A2-2938-4CE3-BC4A-968E0A89C8E2'


The session context can also come in handy if you want to share information between triggers without persisting the information.

You can add your own session variables using the ‘sp_set_session_context‘ stored procedure:

exec sp_set_session_context N'the_answer_to_life', '42';

Be aware that the value is of type sql_variant, so you might have to convert the value before you can use it.



0 replies

Be the first to reply!