Help for SQLServer Session Specific Global Variable

Hi All,

i need to declare and set a global variable that is specific to the
session, each time app logs in to the sqlserver.

for instance,

i have a workers table that is ID,NAME,DEPARTMENTNO

ID NAME DEPARTMENTNO
------------------------------------------------
1 WORKER1 1
1 WORKER2 1
1 WORKER3 2
1 WORKER4 2
1 WORKER5 3

up to now, each user has rules to see workers on one department such
as SELECT * from worker where DEPARTMENTNO=2
im doing this with query code in application, with lots of '..where
DEPARTMENTNO='+inttostr(mydeptno)
my application is connecting to the server and staying connected.

now im thinking of a technic for doing this in sqlserver side;

is there a way like:

DECLARE @DEPTNO int ---variable will be static for my session
SET @DEPTNO=1 ---i will do this first for my
connection session..

-----
and i will have a view like;
SELECT ID,NAME from worker where DEPARTMENTNO=@DEPTNO

notes:
1) there are lots of users on application, all are connectiong with sa
sqlserver user (so have no chance to create an sqlserver user for all
app users)
2) DECLARE @DEPTNO int is working just for that query, i need one,
working for all queries on that session
3) there are lots of discrete clients, so writing DEPTNO data in a
table on each login fails

Any idea?
Thanks all

EnKaradag

EDIT:

Table is like;

ID * NAME * *DEPARTMENTNO
------------------------------------------------
1 * *WORKER1 * * *1
2 * *WORKER2 * * *1
3 * *WORKER3 * * *2
4 * *WORKER4 * * *2
5 * *WORKER5 * * *3

sorry

Ender Karada? (enkaradag@gmail.com) writes:
> up to now, each user has rules to see workers on one department such
> as SELECT * from worker where DEPARTMENTNO=2
> im doing this with query code in application, with lots of '..where
> DEPARTMENTNO='+inttostr(mydeptno)

Which is not the way you should write it. If you are using .Net, you should
write it as

SELECT * FROM worker WHERE DEPARRTMENTNO = @depto

And then pass the value of @depto in the SqlParameters collection.

With several other client API, you use ? as the parameter marker, but
the principle is the same. You should never build complete query strings
from input values. This introduces a risk for SQL injection, and it
utilises the cache in SQL Server poorly. It also gives you headache
with datetime values.


> now im thinking of a technic for doing this in sqlserver side;
>
> is there a way like:
>
> DECLARE @DEPTNO int ---variable will be static for my session
> SET @DEPTNO=1 ---i will do this first for my
> connection session..
>
> -----
> and i will have a view like;
> SELECT ID,NAME from worker where DEPARTMENTNO=@DEPTNO

You could use a temp table that you create on session level. The temp
table would exist until you disconnect.

But this assumes that you keep a global connection that stays active
all the time. That is not a very common application design these days.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

thanks for reply

>Which is not the way you should write it. If you are using .Net, you should
>write it as
> SELECT * FROM worker WHERE DEPARRTMENTNO = @depto
>And then pass the value of @depto in the SqlParameters collection.

im using delphi win32 and actually im doing this job same as u
discribe i just wanted to simplfy the situation.

im dreaming of opening new query editor in sqlserver management studio
and writing down:

SET @DEPTNO=1;
GO

SELECT * from worker
GO
--results results results (filtered with deptno)

SET @DEPTNO=2;
SELECT * from worker
GO
--results results results (filtered with new deptno)

if i do this without "GO"es, its working (same query block stuff)

how is a session level temp table created? (standart "create table"?)
i think this may solve my situation

Ender Karada? (enkaradag@gmail.com) writes:
> how is a session level temp table created? (standart "create table"?)

CREATE TABLE #temp(a int NOT NULL)

But as I said, you need to keep the connection open. Which is what you
do in an Mgmt Studio, but typically not an application.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

thanks for your advice Erland,

i tried, temp table structure is a solution for my situation. once a
temp table is created, it stays live during the connection. my app
keeps connected always, so everything is ok.

i was happy since i saw "Views or functions are not allowed on
temporary tables" message:/ /server doesnt mind tough existance of
that table is also checked in code/

so temp tables are usesless for me

wat about: a real table with SESSIONID (int),DEPTNO (int). for each
connection, connection will insert one row to that two columned real
table,
so view will be like "select w.ID,w.NAME from worker w,mysessions m
where w.DEPARTMENTNO=m.DEPTNO and m.SESSIONID=GET_SESSION_ID (watever
for getcurrentsessionid)

or again for each connection, connection will insert one row to that
two columned real table, view will be like
"..m.DEPTNO=SOMEFUNCTIONTHATCALCULATESMYSESSIONDEP TNO()"

or maybe i shall give up thinking about that

Ender Karada? (enkaradag@gmail.com) writes:
> wat about: a real table with SESSIONID (int),DEPTNO (int). for each
> connection, connection will insert one row to that two columned real
> table,
> so view will be like "select w.ID,w.NAME from worker w,mysessions m
> where w.DEPARTMENTNO=m.DEPTNO and m.SESSIONID=GET_SESSION_ID (watever
> for getcurrentsessionid)
>
> or again for each connection, connection will insert one row to that
> two columned real table, view will be like
> "..m.DEPTNO=SOMEFUNCTIONTHATCALCULATESMYSESSIONDEP TNO()"

Then you need a means to clean them up. (Connections do not always die
in an ordered fashion.)

> or maybe i shall give up thinking about that

In honesty, I think the solutions you are looking at will give you more
headache that what you have now.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

i have a new table dbo.loginhistory with columns
ID,CONNECTIONID,USERNAME,DEPTNO. each time app logs in, app calls an
sql function LOGMEIN(USERNAME,DEPTNO). function inserts a row into
loginhistory table, with the related connection_id uniqueidentifier
value.

after that i created a view like "select ID,NAME from worker where
DEPTNO=GETMYSESSIONDEPTNO()" (GETMYSESSIONDEPTNO() is an sql function
that returns related DEPTNO value in history table)

now im ok with my problem, additionally i have a login history table
after all

wat about solution? will it be problem for a crowded table?

Ender Karada? (enkaradag@gmail.com) writes:
> after that i created a view like "select ID,NAME from worker where
> DEPTNO=GETMYSESSIONDEPTNO()" (GETMYSESSIONDEPTNO() is an sql function
> that returns related DEPTNO value in history table)

A word of warning: data access from user-defined functions can cause
serious performance problem. This one looks innocent, but you should
certainly be careful with using functions all over the place.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx