How to store the ROW_number into the variable in SQL 2008?

[jacksantho]

Hi,

Trying this Query,

[B]DECLARE @SR VARCHAR(8),@SC CHAR(15),@ST CHAR(15),@SNO int;
 SELECT * FROM (SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY a.rno) AS rowid,
@SR=a.rno,@SC=a.kat,
@ST=a.type FROM SMas a,
 SDet b WHERE 
 b.id='X' AND
 a.rno=b.rno) AS C WHERE C.rowid=4

[/B]

but getting this following error:

[B][I][U]Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='. (In this area,@SR=a.rno,@SC=a.kat,
@ST=a.type)
[/U][/I][/B]

Please help me out. Thanks in advance
[/I][/B]

[adam_k]

I don't have an SQL 2008 to test, but I'm guessing that your query fails for a number of reasons.
The 1st one is that you are assigning values to variables inside the nested query.
The 2nd one would be that distinct and row_number don't mix very well as row_number will make every record unique.

Have you tried:

DECLARE @SR VARCHAR(8),@SC CHAR(15),@ST CHAR(15),@SNO int;
select @SR=c.rno,@SC=c.kat,@ST=c.type, @SNO = c.rowid --I'm guessing @SNO is for rowid
from (SELECT ROW_NUMBER() OVER (ORDER BY a.rno) AS rowid,
a.rno,a.kat,a.type 
FROM SMas a, SDet b
WHERE  b.id='X' AND
 a.rno=b.rno ) c  
WHERE C.rowid=4

[BitBlt]

I came up with nearly the same query as @adam_k, but he beat me to posting. Only difference between his and mine is that I included the DISTINCT clause. And I did test it with fake tables/data and it worked fine both ways.

[jacksantho]

HI,

Adam k and BitBit . Really Thanks for your guidance and for your help. It's too working for me.