7
\$\begingroup\$

Based on my question here, I have updated my query to take user input so it can be a generic query:

DECLARE @location AS nvarchar(max) = ##location:string##;
SELECT u.Id [User Link], u.Reputation, u.Location
FROM Users u
WHERE lower(u.Location) LIKE lower('%' + @location + '%')
ORDER BY 'Reputation' DESC

Is there something I should be doing differently, or is this good?

\$\endgroup\$
  • 2
    \$\begingroup\$ As d347hm4n's answer points out, we should be careful about our column types. Users.Location is NVarChar(100). \$\endgroup\$ – nhgrif Apr 21 '15 at 10:39
4
\$\begingroup\$

This looks fine, the only thing I would say is @location is being declared as nvarchar(max). I can only assume that u.Location is also nvarchar(max). If it isn't then set it to be the same size. nvarchar max will have a slight performance hit to it.

\$\endgroup\$
2
\$\begingroup\$

There's no need to add a table alias when you are only dealing with a single table.

Also there's no need to worry about LOWER as the columns have a case insensitive collation.

Fixing the datatype issue mentioned in the comments and removing the unnecessary other stuff mentioned above you would end up with.

DECLARE @location AS NVARCHAR(100) = ##location:string##;

SELECT Id AS [User Link],
       Reputation,
       Location
FROM   Users
WHERE  Location LIKE '%' + @location + '%'
ORDER  BY Reputation DESC 

One other thing to consider is if the input itself might contain any characters of special significance in the LIKE pattern syntax.

It is unlikely that any legitimate locations will contain characters such as %, _, [.

If you do want to correctly support searches for these strings you would need to either ESCAPE them all or just use CHARINDEX instead.

SELECT Id AS [User Link],
       Reputation,
       Location
FROM   Users
WHERE CHARINDEX(@location, Location) > 0
ORDER  BY Reputation DESC 
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.