Groups > comp.databases.ms-sqlserver > #708

Re: Help for SQLServer Session Specific Global Variable

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> (permalink)
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

Show key headers only | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Help for SQLServer Session Specific Global Variable Ender Karadağ <enkaradag@gmail.com> - 2011-10-02 10:18 -0700
  Re: Help for SQLServer Session Specific Global Variable Ender Karadağ <enkaradag@gmail.com> - 2011-10-02 10:21 -0700
  Re: Help for SQLServer Session Specific Global Variable Erland Sommarskog <esquel@sommarskog.se> - 2011-10-02 19:37 +0200
    Re: Help for SQLServer Session Specific Global Variable Ender Karadağ <enkaradag@gmail.com> - 2011-10-02 10:56 -0700
      Re: Help for SQLServer Session Specific Global Variable Erland Sommarskog <esquel@sommarskog.se> - 2011-10-02 20:28 +0200
        Re: Help for SQLServer Session Specific Global Variable Ender Karadağ <enkaradag@gmail.com> - 2011-10-02 17:30 -0700
          Re: Help for SQLServer Session Specific Global Variable Erland Sommarskog <esquel@sommarskog.se> - 2011-10-03 07:16 +0000
            Re: Help for SQLServer Session Specific Global Variable Ender Karadağ <enkaradag@gmail.com> - 2011-10-03 08:57 -0700
              Re: Help for SQLServer Session Specific Global Variable Erland Sommarskog <esquel@sommarskog.se> - 2011-10-03 23:14 +0200

csiph-web