I am developing a local windows application that uses SQL Server. Each user has a login. There is also a store id. The user can login and be assigned their default store id but can also switch to other stores without having to logout so the store id can change.

Any records that are generated have a column for the store ID in all tables.

What I was hoping to do was the following.

Make the Store ID column a non null and set the Default Value or Binding to a SQL Function.

That SQL Function would retrieve the currently selected StoreID for insertion in the field.

What is the best way to ensure the StoreID is accurate? I could create a temp table that links the user id to the currently selected store ID. I am not well versed enough in SQL server to know if this is the best approach or if there is something much simpler. I have also tried searching but the results have been weak as its likely I am not sure how to couch my search terms in a manner that will return accurate results.

So far I have tried nothing, I am just wondering if this ia a feasible approach.

EDIT

Oh good point. In re-reading I realized StoreID is somewhat ambilvalent. Iny my case StoreID is just a way of identifying a store front. When the user logins they are assigned to a default store front. Any actions they perform are ascribed to their selected store front . Essentially each table has a StoreID column. Now, without logging out they can switch to a different store front. That storeid is retrieved from the main Store table. The storeid is simply stored in in a variable within the application. Anytime they insert or update to a table the StoreID has to be part of the query. I was just wondering if the StoreID field could be set to a DefaultValue that changes. It can by using a call to a function . But that function still has to somehow retreive the StoreID held as a variable in the application. Knowing my nature I will probably devise some huge, overblown method of accomplishing this task but I inevitably after I spend some time building , I discover someone has posted a very simple and elegant way to accomplish what has taken me too long to devise.

I am not sure how to improve clarity. But le me try.

I have a column in a table. That column requires a Default Value or Binding property. That Default Binding is linked to a Function like so

enter image description here

However that function requires an input, which would be the StoreID. The function cannot receive the input when called in this fashion as I do not think the action that occurs when the default value for the column is inserted can accept an external parameter.