SQL Server Ordered Append Syntax

[yaanmonkey]

Hi,

I am looking for a solution to this problem:

I would like to create a stored proc that will accept certain criteria parameters (E.g. Letter from... Where LastName >=@LetterFrom or similar).

I also need to be able to specify the TOP 'x' number of rows depending on a user entered parameter.

But the hard bit is:
I also want to ORDER BY Surname before appending the data. So...

I would like to do something like:

SET rowcount(@Top)

INSERT INTO Table2 (FirstName, Surname, Address1, Address2)
SELECT FirstName, Surname, Address1, Address2
FROM Table1
WHERE Surname LIKE @Surname + '%'
ORDER BY Surname

But i thinkl the ORDER BY statement will cause an issue. I don't know if there is a 2-stage way of doing it where the 1st part of the stored proc will return an ordered recordset then the second part can apply the filter nad limit to TOP 'x' rows

Any help will be greatly appreciated.

many thanks,

Ian

 

[Lamprey13]

The ORDER BY will be applied, then the TOP ## will be applied. So, if I follow you, that is what you want and should work just fine. Did you try this already and not get the results you want?