Variables Global To Current Connection

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Variables Global To Current Connection

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-14 : 08:29:36

Larry writes "Win XP (clients); SQL Server 2000 SP3

In most tables in a DB, I have a [Location ID #] field (int data type) which stores the branch office ID of the user who created the record. I would like for each user's SQL Server connection to store his/her [Location ID #] so that connected user will see records only from his/her branch. Some functions or stored procedures in this DB used to return resultsets would reference that variable in their SQL statement WHERE clause to filter:
(stored procedure snippet)
SELECT * FROM TableName WHERE [Location ID #] = [Connection Scoped Variable]

I've looked at using SET CONTEXT_INFO in a procedure which determines the location and calls SET CONTEXT_INFO to store it, but that uses a binary data type which does not CAST to an int type (Books Online says CAST may return different values in subsequent calls because of conversion from binary type).

This value could be stored in each user's running client, but the value would have to be a parameter to each request for data and thus would require more network traffic. It would be more efficient if this could all be done on the server.

Any suggestions?

Thanks In Advance"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-14 : 09:12:41

Since your application knows the locationID (for inserts) why not just have it append the parameter collection with a @locationID parameter for selects. Your select SPs would obviously now have a @LocationID parameter.

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-14 : 09:59:38

This seems to work to store an integer and get it back:


declare @num	int
declare @CONTEXT_INFO	varbinary(128)
select 	@num  = 789123456

select 	@CONTEXT_INFO  = convert(varbinary(128),@num)
select 	@CONTEXT_INFO 

select 	convert(int,@CONTEXT_INFO)  

SET CONTEXT_INFO @CONTEXT_INFO

SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

SELECT convert(int,convert(varbinary(4),context_info))
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-14 : 10:22:36

Sorry Larry, I re-read your question. When you said, "would require more network traffic", I assumed you were tring to save yourself a round trip rather than reduce the size of your request by 1 parameter. That is an interesting idea, to save that value as context info for each connection so you aren't passing it in for every request. Typically, the execution time and the amount of data being returned with the request are the big factors. Do you expect this will make a significant difference? I'm not challenging the idea, I'm genuinely curious. (That's cool, MVJ, nice)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources