Trigger Procedure Doubt - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger Procedure Doubt

Author

Topic

shown_sunny
Starting Member

9 Posts

Posted - 2004-05-26 : 08:27:03

dear friends,

Query1
======
********************************************************************
SELECT @colvalue=column_name from information_schema.columns where ordinal_position=@cont and table_name=@tablename
********************************************************************

Query2
======
********************************************************************
select (select t.@colvalue from #trans_col t)
from information_schema.columns where ordinal_position=@cont and table_name=@tablename
*************************************************************

1)These are the two queries within a procedure.

2)The solution I like to have is to get the value of a column_name.

3)The first query selects the corresponding name of a field .

4)What I wan't in the second query is to put this fieldname in the query and obtain the value of the corresponding field.

But this within the second query
***************************************
(select t.@colvalue from #trans_col t)
**************************************
doesn't work.

5)when i change the @ from colvalue ,the query returns only column name.

6)Is there a function in SQL SERVER to get the content of a fieldname

regards
shown_sunny

dsdeming

479 Posts

Posted - 2004-05-26 : 08:46:58

You need to use dynamic sql for that. Something like this:

DECLARE @cmd varchar( 1000 )
SET @cmd = 'select t.' + @colvalue + ' from #trans_col t'
EXECUTE ( @cmd )

Dennis

shown_sunny
Starting Member

9 Posts

Posted - 2004-05-27 : 02:21:22

dear desdeming,

Thank u for the answer.

I had one more doubt regarding the same.How do we store the result of the dynamic sql in a variable.

regards
shown_sunny

dsdeming

479 Posts

Posted - 2004-05-27 : 08:47:22

Take a look at sp_executesql in BOL. It's possible to retrieve output from dynamic sql using sp_executesql.

DECLARE @i int,
@nvcCommand nvarchar( 1000 ),
@sysTableName sysname

SET @sysTableName = 'sysobjects'
SET @nvcCommand = 'SELECT @i = COUNT( * ) FROM ' + @sysTableName

EXECUTE sp_ExecuteSql @nvcCommand, N'@i int OUTPUT', @i OUTPUT

SELECT ObjectCount = @i

Dennis

Kristen
Test

22859 Posts

Posted - 2004-05-27 : 14:20:11

quote:
Originally posted by shown_sunny

How do we store the result of the dynamic sql in a variable.

In addition to sp_executesql: if the dynamic SQL is only producing one resultset you can store the restuls in a table:


CREATE TABLE #KRISTEN1
(
   Foo1 varchar(10),
   Bar1 int
)

DECLARE @strSQL varchar(8000)

SELECT @strSQL = 'SELECT [Foo2] = ''foo'', [Bar2] = 1 UNION ALL SELECT ''bar2'', 2'

INSERT INTO #KRISTEN1
EXEC (@strSQL)

SELECT *
FROM #KRISTEN1

DROP TABLE #KRISTEN1

Kristen

shown_sunny
Starting Member

9 Posts

Posted - 2004-05-31 : 05:17:36

thank u dsdeming,

U'r technique worked.

Hoping for u'r advice in the future too.

regards
shown_sunny

shown_sunny
Starting Member

9 Posts

Posted - 2004-05-31 : 05:19:17

thank u kristen,

The technique described by u was of very good help to me.Hoping for u'r advice in the future too.

regards
shown_sunny

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources