Store result[index] from table in local var

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 2005 Forums

Transact-SQL (2005)

Store result[index] from table in local var

Author

Topic

BorisCallens
Yak Posting Veteran

50 Posts

Posted - 2008-04-30 : 04:57:12

Hi,

Can I take the [index] result from a table and store it in a local var?
For example a table

declare @keyWords(
word varchar(40),
wordLen smallint
)

word len
---- ---
test 4
t 1
e 1

declare @index smallInt = 2
SET @keyWord = SELECT TOP @INDEX word FROM @KeyWords

This obviously is not correct for several reasons (TOP returns multiple results, TOP doesn't work with local var etc)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-30 : 05:01:38

Yes. Add identity column to the table or use new ROW_NUMBER() function.

declare @t table
(
id int identity(1,1),
word varchar(40),
wordLen smallint
)

insert @t(word, wordlen)
select 'test', 4 union all
select 't', 1 union all
select 'e', 1

select * from @t where id = 2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 05:02:36

[code]SELECT t.word.t.len
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY len DESC) AS RowNo,
word,
len
FROM @keyWords
)t
Where t.RowNo=@index[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources