Passing information from client to SQL Server

Is there a means to pass information to SQL server (on a single connection)
that is accessible from T-SQL or triggers. My scenario is that when certain
tables are updated/inserted/deleted, I am making entries in a "log" table,
via triggers, that contains the table name and the action performed on it.
What I also need to insert into the logging table is an integer denoting som
e
additional information about the update/insert/delete which needs to come
from the client. Also, this information needs to be scoped to the connectio
n
so that it is not accesible from other connections.What is the additional info? Some things come for free that might be useful
for your scenario eg HOST_NAME(), HOST_ID() but if this is something
specific to your business, then perhaps it needs to be an additional field
on each table, and the triggers will pick it up that way.
Cheers,
Paul Ibison|||Maybe using context_info is an option you can use in your
scenario. Refer to the following books online topic for more
info:
SQL 2000
http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
or
SQL 2005
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
-Sue
On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
<arothberg@.newsgroups.nospam> wrote:

>Is there a means to pass information to SQL server (on a single connection)
>that is accessible from T-SQL or triggers. My scenario is that when certai
n
>tables are updated/inserted/deleted, I am making entries in a "log" table,
>via triggers, that contains the table name and the action performed on it.
>What I also need to insert into the logging table is an integer denoting so
me
>additional information about the update/insert/delete which needs to come
>from the client. Also, this information needs to be scoped to the connecti
on
>so that it is not accesible from other connections.|||This is exactly what I needed. Thanks.
"Sue Hoegemeier" wrote:

> Maybe using context_info is an option you can use in your
> scenario. Refer to the following books online topic for more
> info:
> SQL 2000
> http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
> or
> SQL 2005
> http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
> -Sue
> On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
> <arothberg@.newsgroups.nospam> wrote:
>
>

Passing information from client to SQL Server

Is there a means to pass information to SQL server (on a single connection)
that is accessible from T-SQL or triggers. My scenario is that when certain
tables are updated/inserted/deleted, I am making entries in a "log" table,
via triggers, that contains the table name and the action performed on it.
What I also need to insert into the logging table is an integer denoting some
additional information about the update/insert/delete which needs to come
from the client. Also, this information needs to be scoped to the connection
so that it is not accesible from other connections.What is the additional info? Some things come for free that might be useful
for your scenario eg HOST_NAME(), HOST_ID() but if this is something
specific to your business, then perhaps it needs to be an additional field
on each table, and the triggers will pick it up that way.
Cheers,
Paul Ibison|||Maybe using context_info is an option you can use in your
scenario. Refer to the following books online topic for more
info:
SQL 2000
http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
or
SQL 2005
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
-Sue
On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
<arothberg@.newsgroups.nospam> wrote:
>Is there a means to pass information to SQL server (on a single connection)
>that is accessible from T-SQL or triggers. My scenario is that when certain
>tables are updated/inserted/deleted, I am making entries in a "log" table,
>via triggers, that contains the table name and the action performed on it.
>What I also need to insert into the logging table is an integer denoting some
>additional information about the update/insert/delete which needs to come
>from the client. Also, this information needs to be scoped to the connection
>so that it is not accesible from other connections.|||This is exactly what I needed. Thanks.
"Sue Hoegemeier" wrote:
> Maybe using context_info is an option you can use in your
> scenario. Refer to the following books online topic for more
> info:
> SQL 2000
> http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
> or
> SQL 2005
> http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
> -Sue
> On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
> <arothberg@.newsgroups.nospam> wrote:
> >Is there a means to pass information to SQL server (on a single connection)
> >that is accessible from T-SQL or triggers. My scenario is that when certain
> >tables are updated/inserted/deleted, I am making entries in a "log" table,
> >via triggers, that contains the table name and the action performed on it.
> >What I also need to insert into the logging table is an integer denoting some
> >additional information about the update/insert/delete which needs to come
> >from the client. Also, this information needs to be scoped to the connection
> >so that it is not accesible from other connections.
>

Passing Column Name as Parameter (SPROC)

Hello, I don't know a lot of T-SQL. I have a table with 49 columns and 48 rows (US States). The fist column is a list of "from" states and the other columns are named for each state. The point is to look up a variable column for a variable row to obtain a state to state transit rate. I tried everything I could find and no luck. If someone can help I would appreciate it, and if not thanks anyway. Gregg

You could use dynamic SQL from this problem:

Code Snippet

create table rates

(

from_state char(2),

WA decimal,

NY decimal,

CA decimal

)

insert into rates values('WA',0,1,10)

insert into rates values('NY',2,0,20)

insert into rates values('CA',3,30,0)

create procedure GetRate

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query varchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state='''+@.from_state+''''

execute ( @.query)

END

But this method has SQL Injections.

Partly you could solve this problem by following code:

Code Snippet

create procedure GetRate2

@.from_state char(2),

@.to_state char(2)

AS

BEGIN

declare @.query nvarchar(100)

set @.query = 'select '+@.to_state+' from rates where from_state=@.from_state'

EXEC sp_executesql @.query, N'@.from_state char(2)', @.from_state=@.from_state

END

But best solution is convert you table for following format:

Code Snippet

create table rates2

(

from_state char(2),

to_state char(2),

rate decimal

)

|||

Here the sample ..

Code Snippet

--Sample table (first 10 States)

Create Table #statesfare (

[Starting From] Varchar(100) ,

[Alabama] Varchar(100) ,

[Alaska] Varchar(100) ,

[Arizona] Varchar(100) ,

[Arkansas] Varchar(100) ,

[California] Varchar(100) ,

[Colorado] Varchar(100) ,

[Connecticut] Varchar(100) ,

[Delaware] Varchar(100) ,

[Florida] Varchar(100) ,

[Georgia] Varchar(100)

);

--Random Generated data (Not For Scale, But City From & To same then ZERO)

Insert Into #statesfare Values('Alabama','0','21','29','90','82','65','72','84','4','51');

Insert Into #statesfare Values('Alaska','33','0','17','80','37','92','90','21','12','20');

Insert Into #statesfare Values('Arizona','62','19','0','97','23','66','22','43','94','60');

Insert Into #statesfare Values('Arkansas','61','38','12','0','98','42','68','70','81','87');

Insert Into #statesfare Values('California','92','27','82','72','0','43','84','39','24','80');

Insert Into #statesfare Values('Colorado','72','34','97','52','52','0','10','38','64','40');

Insert Into #statesfare Values('Connecticut','100','78','27','18','74','3','0','67','26','48');

Insert Into #statesfare Values('Delaware','93','49','20','88','45','3','60','0','57','77');

Insert Into #statesfare Values('Florida','79','80','37','12','90','30','24','48','0','50');

Insert Into #statesfare Values('Georgia','33','46','16','30','46','72','42','85','18','0');

Declare @.StartFrom as varchar(100);

Declare @.EndAt as varchar(100);

--Sample Input

Set @.StartFrom = 'Alabama'

Set @.EndAt = 'Georgia'

--Using Dynamic SQL (Supports Both SQL Server 2000 & 2005)

Exec ('Select ' + @.EndAt + ' Fare From #statesfare Where [Starting From] =''' +@.StartFrom + '''');

--Using UNPIVOT operator Only on SQL Server 2005

Select

[Starting From]

,[Target States]

,Fares

From

#statesfare P

UNPIVOT

(

Fares FOR [Target States] IN

(

[Alabama],

[Alaska],

[Arizona],

[Arkansas],

[California],

[Colorado],

[Connecticut],

[Delaware],

[Florida],

[Georgia]

)

) as UPVT

Where

[Starting From] = @.StartFrom

And [Target States] = @.EndAt

|||

I recommend to use quotename() function to avoid sql injection.

Code Snippet

set @.qeury = 'select ' + quotename(@.to_sate) + ' from ....';

quotename('abc') returns '[abc]';

Regards,

|||This is my table

FROM AL AR AZ CA AL 1.75 1.95 1.10 1.75 AR 1.50 1.50 1.50 1.75 AZ 1.50 1.50 1.50 1.75 CA 1.50 1.50 1.50 1.75

I Need to use a stored procedure to select one of these decimal rates based on two input parameters (@.fromstate AND @.tostate) and return the rate as an out put parameter(@.rate). I tried this even though I knew it was too simple to worrk.

ALTER PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

SELECT @.rate = @.deststate

FROM Rates48

WHERE Origin_State = @.origstate

END

RETURN

I'm sorry if you're previous posts already answered this, I thought I might not have been clear enougn as to my situation and my goal. I really did search a lot on this topic but got nowhere. Again if you can help (or already did) thanks, and if you can't thanks anyway, Gregg|||

here you go...

Code Snippet

Create Table rates48 (

[Origin_State] Varchar(100) ,

[AL] float ,

[AR] float ,

[AZ] float ,

[CA] float

);

Go

Insert Into rates48 Values('AL','1.75','1.95','1.10','1.75');

Insert Into rates48 Values('AR','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('AZ','1.50','1.50','1.50','1.75');

Insert Into rates48 Values('CA','1.50','1.50','1.50','1.75');

Go

Create PROCEDURE RC_Get_Rate

(

@.origstate nvarchar(255),

@.deststate nvarchar(255),

@.rate nvarchar(255) OUTPUT

)

AS

SET NOCOUNT ON

BEGIN

Declare @.Query as NVarchar(1000);

Set @.Query = N'SELECT @.rateout = ' + quotename(@.deststate) + '

FROM Rates48

WHERE Origin_State = @.origstatein';

Exec sp_executesql @.Query, N'@.origstatein varchar(255), @.rateout nvarchar(255) output',@.origstatein = @.origstate, @.rateout=@.rate OUTPUT;

Return;

END

Go

Declare @.rate nvarchar(255)

Exec RC_Get_Rate 'AL', 'AZ', @.rate OUTPUT

Select @.rate

|||Thank You very much Manivannan.D.Sekaran. That works very well. I must admit though I don't know how. Could you maybe recommend a good book I could pick up to further myself on T-SQL. I would like to learn SPROC's, Functions, and some more advanced SQL. Well, again, thank you very much Manivannan.D.Sekaran, and also thank you to everyone who took the time to read my post and help me out. I really appreciate it. Thanks, Gregg