Careful with Session_Context()–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

A quick note, which is more of a reminder to myself. I find writing things down helps me remember, and I need to remember this.

I was working with session context, specifically the SESSION_CONTEXT() function. When using this function, you give it a key that contains a value, like this:

2019-01-15 13_45_34-RLS_Testing.sql - Plato_SQL2016.WideWorldImporters (PLATO_Steve (61))_ - Microso

I get a value back that I can use. Everything is great.  I was using this to allow one process to set a value and another to get it, and I was happy.

Until things stopped working. While trying to debug this, I ran this code:

2019-01-15 13_46_46-RLS_Testing.sql - Plato_SQL2016.WideWorldImporters (PLATO_Steve (61))_ - Microso

Notice a difference? In the first query, I have SupplierID, but the second is SupplierId, with a lower case “d”. These keys are determined when you use sp_set_session_context, which takes a sysname value for the key. These are going to be case sensitive, as each one is a different identifier.

It’s not likely that this will cause lots of problems, but when you are setting keys, be careful and ensure you use the same value for writing and reading.

SQLNewBlogger

This was a quick mistake I made and it took me 5 minutes to write up. It’s helpful to get me to remember to avoid this, but this also shows I can fix my mistakes.

What’s a simple thing you learned that makes you write better code? Write your own SQLNewBlogger post today.

Leave a Reply

Your email address will not be published. Required fields are marked *