Is it possible to create a custom SQL session function/variable

To avoid having to pass this user specific ID via a parameter in the stored
procedure, is it possible to create your own function like (like
suser_sid()) or variable (like @.@.SPID) ?
This value would be passed from a Web Service (asp.net 2.0) in the
connection string or something and could be referred to anywhere in the SQL
code (SQL 2005).
Has anyone got a better solution than a SP parameter ?Asked and answered in .programming. Please refrain from multiposting.
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:%23cNc2ceuHHA.3376@.TK2MSFTNGP04.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>|||Take a look at SET CONTEXT_INFO in the Books Online. That allows you to set
a value for the connection that is accessible anywhere in the session with
the CONTEXT_INFO function. Since the value is binary, you'll need to
convert as needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:%23cNc2ceuHHA.3376@.TK2MSFTNGP04.phx.gbl...
> To avoid having to pass this user specific ID via a parameter in the
> stored
> procedure, is it possible to create your own function like (like
> suser_sid()) or variable (like @.@.SPID) ?
> This value would be passed from a Web Service (asp.net 2.0) in the
> connection string or something and could be referred to anywhere in the
> SQL
> code (SQL 2005).
> Has anyone got a better solution than a SP parameter ?
>