Retrieve the top X random rows from a query
Let’s suppose for a minute that you want to retrieve the top x number of rows from any given query. Keep in mind that x is a value that your users can supply.
Sure, you could do string concatenation and EXEC sp_executesql ‘SELECT TOP ‘ + @count + ‘ x FROM y;’. But, then again, a vicious hacker could get all cute and decide to change your form variable to ‘; DECLARE @bad_var AS NVARCHAR(MAX); SET @bad_var = ””; SELECT @bad_var = @bad_var”DROP TABLE ” + name + ”;” FROM sys.tables; SET @bad_var = @bad_var + ”–”; EXEC sp_executesql @bad_var;’ Or something along those lines but probably infinitely more cunning.
Well, you could also accomplish this a different way using a subquery:
DECLARE @count AS int; SELECT @count = 2; SELECT ContactId, FirstName, LastName, EmployeeId, AddressId, AddressLine1, AddressLine2, City, PostalCode FROM ( SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS rownum, c.ContactId, c.FirstName, c.LastName, e.EmployeeId, a.AddressId, a.AddressLine1, a.AddressLine2, a.City, a.PostalCode FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON e.ContactId = c.ContactId INNER JOIN HumanResources.EmployeeAddress AS ea ON e.EmployeeId = ea.EmployeeId INNER JOIN Person.Address AS a ON ea.AddressId = a.AddressId ) AS x WHERE rownum <= @count;
It’s not the prettiest thing on earth, but it works. Thanks to Rick Kierner over atrickdoes.net for asking me to help out with a similar query late last week.
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.