Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!feeder.news-service.com!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Help for SQLServer Session Specific Global Variable
Date: Sun, 02 Oct 2011 19:37:11 +0200
Organization: Erland Sommarskog
Lines: 46
Message-ID: <Xns9F72C795AB69AYazorman@127.0.0.1>
References: <98153dbd-72d4-41da-8e97-fb3f6c1ff710@z19g2000vby.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx04.eternal-september.org; posting-host="nBFDv6s1VJQDuF1w6hpX2A"; logging-data="21680"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1/j09hz4Z9usk5b5gB3JFZ+"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:FOaWiAXe9pcqSN4BVou5Sy1+93k=
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:708

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