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 ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.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 ?
>

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 ?Take a look at CONTEXT_INFO in the Books Online. This allows you to store
up to 128 bytes of binary info that can be used anywhere in the session.
Hope this helps.
Dan Guzman
SQL Server MVP
"GMG" <gmgsoftware@.nospam.net.au> wrote in message
news:uI04I9duHHA.4440@.TK2MSFTNGP06.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 ?
>sql

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 ?
>