Facility9

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 at rickdoes.net for asking me to help out with a similar query late last week.