Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: Help for SQLServer Session Specific Global Variable

Show all headers | 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