RUN a stored procedure or function for every SQL session automatically

Question

RUN a stored procedure or function for every SQL session automatically

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
April 10, 2018 at 8:10 am

#403238

Is there a way to run a stored procedure or a function for every new sql session automatically.
This SP or function sets up certain parameters which is getting used for database.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

skb 44459 - Tuesday, April 10, 2018 8:10 AM
Is there a way to run a stored procedure or a function for every new sql session automatically.
This SP or function sets up certain parameters which is getting used for database.

You could use a server-scoped DDL trigger to run the procedure at login.
Technically, a function can't be run, just called. Functions are used to return a value, not to change things in the database.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 2

I would like to have it at database level. Depending on what database is getting accessed, I would like to set parameters.

Let me give some background.
We use a ERP System , In the current version we have a table called CO.
In the New release they replaced CO table with CO_MST table and created a view on CO_MST
View definition looks like . create view co as select * from CO_MST where site_ref = CONTEXT_INFO()

That's why I wanted to set a CONTEXT_INFO() for every SQL session automatically (I was planning to use stored procedure) so that my query (select * from co) will work.
I would like to set CONTEXT_INFO() based on the database in use.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

skb 44459 - Tuesday, April 10, 2018 9:21 AM
I would like to have it at database level. Depending on what database is getting accessed, I would like to set parameters.
Let me give some background.
We use a ERP System , In the current version we have a table called CO.
In the New release they replaced CO table with CO_MST table and created a view on CO_MST
View definition looks like . create view co as select * from CO_MST where site_ref = CONTEXT_INFO()
That's why I wanted to set a CONTEXT_INFO() for every SQL session automatically (I was planning to use stored procedure) so that my query (select * from co) will work.
I would like to set CONTEXT_INFO() based on the database in use.

Someone correct me if I don't have it right, but I'm pretty sure you'd need to set that in the application that creates the session.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)