help with query!

I have 2 tables

tblStyles
StyleID int (pk)
XMLfilename nvarchar(50)

tblRules
RuleID int (pk)
Usercode int
StyleID int (fk)
ruleindex tinyint

Now in a stored proc I want to retreive XMLfilename for the first rule that matches certain criteria.
So if Usercode 5 would have 3 rules defined with ruleindex: 8, 6 and 7 (they might be in that order in the table)
and both rule 6 and 8 would match my criteria, I would like to have the xmlfilename related to the rulerecord with ruleindex 6 (as 6 is the lowest ruleindex).

Here's what I have so far:
----------
declare @.style nvarchar(50)

set @.style=(

select XMLfileid FROM (

select us.XMLfileid,r.ruleindex from tblRules r
inner join tblUserStyles us on us.StyleID=r.StyleID
wherer.usercode=@.Usercode
order by ruleindex

) as tmp

WHERE <allmycriteria>

)
----------

I get this error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

declare @.style nvarchar(50)

set @.style=(

select XMLfileid FROM (

select Top 1 us.XMLfileid,r.ruleindex from tblRules r
inner join tblUserStyles us on us.StyleID=r.StyleID
wherer.usercode=@.Usercode
order by ruleindex asc

) as tmp

WHERE <allmycriteria>

)

Coz u need only one file name of the samlest index matched . You cannot use Order By in a Sub Query without using Top

alternative to your query you can use

1Create Procedure GetXmlFile2(3@.UserCodevarchar(10)4)5AS67BEGIN89declare @.stylenvarchar(50)1011set @.style=(1213select XMLfileidFROM tblStyleswhere StyleIdin(14Select Top 1 TR.StyleIdFROM15tblRulesINNERJOIN tblStyle TSon TS.StyleId=TR.StyleId16Where TR.usercode=@.UserCode17order by Tr.ruleindexasc18)1920WHERE21Select @.Style22END23

|||

It seems to me that having the scalar value assigned in the stored procedure is overkill. Review this and let me know if it works for you:

Create Procedure GetXmlFile( @.UserCodevarchar(10))ASBEGIN SELECT ts.XMLfileidFROM tblStyles tsWHERE ts.StyleId = (select min(tr.StyleId)from tblRules trwhere tr.usercode = @.UserCodegroup by tr.usercode )END