[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,
@ST=a.type FROM SMas a,
SDet b WHERE
a.rno=b.rno) AS C WHERE C.rowid=4
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,
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,
FROM SMas a, SDet b
WHERE b.id='X' AND
a.rno=b.rno ) c
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.