Where Is the Syntax Error in this SQL?

Question

I've got a query that is pretty much the same as many others which are used in the same library... but I did a lot of copy&paste on the SQL to add features to each one which are all similar but slightly different. Just below is the section which gives me the SQL Parser error. It fires at the Set rs = line.

dim sql, rs
sql = "DECLARE @str VARCHAR(255); SELECT @str = LOWER(?);" &_
      "SELECT * ( " &_
        "SELECT TOP 8 * FROM [oca_search_model] WHERE " &_
        "[osm_isactive] = 1 AND " &_
        "LOWER([osm_category]) = LOWER(?) AND " &_
        "(LOWER([osm_keywords]) LIKE '%'+@str+'%' OR " &_
        "LOWER([osm_description]) LIKE '%'+@str+'%' OR " &_
        "LOWER([osm_name]) LIKE @str+'%') " &_
        "ORDER BY [osm_weight] DESC" &_
      ") AS T1 ORDER BY [T1].[osm_category] ASC, [osm_weight] DESC, [osm_name] ASC;"
Set rs = executeQuery(conn, sql, Array(searchString, category))

The specific error I receive is: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'. I have printed out the SQL that is generated from that concatenation and it is as follows (with added line breaks):

DECLARE @str VARCHAR(255);
SELECT @str = LOWER(?);
SELECT * ( 
  SELECT TOP 8 * FROM [oca_search_model]
  WHERE [osm_isactive] = 1
  AND LOWER([osm_category]) = LOWER(?)
  AND (
    LOWER([osm_keywords]) LIKE '%'+@str+'%'
    OR LOWER([osm_description]) LIKE '%'+@str+'%'
    OR LOWER([osm_name]) LIKE @str+'%'
  )
  ORDER BY [osm_weight] DESC
) AS T1
ORDER BY [T1].[osm_category] ASC, [osm_weight] DESC, [osm_name] ASC;

For your reference, I have added the executeQuery function below.

Function executeQuery(ByRef connection, ByRef querytext, ByRef parameters)
  Dim cmd, i, rs
  Set cmd = Server.CreateObject("ADODB.Command")
  cmd.CommandText = querytext
  cmd.CommandType = 1
  cmd.Prepared = True
  For i = 0 To UBound(parameters)
    cmd.Parameters.Append(createVarCharInputParameter(cmd, "", parameters(i)))
  Next
  Set cmd.ActiveConnection = connection 
  Set rs = cmd.Execute()
  Set executeQuery = rs
End Function

I don't have access to run the query directly on the server with some test values. But a similar query without the LOWER([osm_category]) = LOWER(?) AND portion runs just fine. Can you spot the syntax error in that SQL? My colleagues and I can't seem to.

Please note that it is imperative that I retain the ordering of the top 8 records by the osm_weight field. More specifically, the query needs to: get the top 8 by weight that match the category, and string (and are active). Then I need to order them by category so they're "grouped" and then within each category I need them ordered by weight and then by name.

Answer 1

The error is in this section:

AND (
    LOWER([osm_keywords]) LIKE '%'+@str+'%'
    OR LOWER([osm_description]) LIKE '%'+@str+'%'
    OR LOWER([osm_name]) LIKE @str+'%'
) ORDER BY [osm_weight] DESC
) AS T1

The AND is part of the where query, and you can use parenthesis here, no problem. Bu the ORDER BY seems to add a set of mismatched Parenthesis.

This query should (syntactically at least) work:

DECLARE @str VARCHAR(255);
SELECT @str = LOWER(?);
SELECT TOP 8 * FROM [oca_search_model]
WHERE [osm_isactive] = 1
AND LOWER([osm_category]) = LOWER(?)
AND (
    LOWER([osm_keywords]) LIKE '%'+@str+'%'
    OR LOWER([osm_description]) LIKE '%'+@str+'%'
    OR LOWER([osm_name]) LIKE @str+'%'
) 
ORDER BY [osm_weight] DESC

Answer 2

You're missing the FROM after the first SELECT *.

DECLARE @str VARCHAR(255);
SELECT @str = LOWER(?);
SELECT * FROM ( 
  SELECT TOP 8 * FROM [oca_search_model]
  WHERE [osm_isactive] = 1
  AND LOWER([osm_category]) = LOWER(?)
  AND (
    LOWER([osm_keywords]) LIKE '%'+@str+'%'
    OR LOWER([osm_description]) LIKE '%'+@str+'%'
    OR LOWER([osm_name]) LIKE @str+'%'
  )
  ORDER BY [osm_weight] DESC
) AS T1
ORDER BY [T1].[osm_category] ASC, [osm_weight] DESC, [osm_name] ASC;