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;