MSSQL parameterized query causes "Incorrect syntax near the keyword 'from'"

Question

I have a parameterized query that looks like this:

SELECT title, html, shortname FROM dbo.Videos
WHERE Topic_ID = ? AND dbo.gradeLevelCheck(?, Grade_Lower, Grade_Upper) = 1
ORDER BY shortname ASC

When I run it from ASP, I get an error that reads:

Incorrect syntax near the keyword 'from'

The parameters are 56 and 1 (so no nulls). The stored function dbo.gradeLevelCheck looks like this:

ALTER FUNCTION [dbo].[gradeLevelCheck]
(
    @value int,
    @lower int,
    @upper int
)
RETURNS int
AS
BEGIN
    DECLARE @result int;
    IF (@lower IS NULL OR @lower <= @value) AND (@upper IS NULL OR @upper >= @value)
        SET @result = 1;
    ELSE
        SET @result = 0;
    RETURN @result;
END

It works fine if I submit it with the parameters hardcoded into the query. It also works fine if I remove the ORDER BY clause. But I can't get it to work in this form. Can anyone help me figure out why?

EDIT: Here are some additional details. The application is written in Classic ASP with JScript. The connection is established thus:

        var conn = Server.CreateObject('ADODB.Connection');
        var connectionString =
            'Provider=SQLOLEDB;' +
            'Data Source=' + Settings.dbServer + ';' +
            'Initial Catalog=' + Settings.dbCatalog + ';' +
            'User Id=' + Settings.dbUser + ';' +
            'Password=' + Settings.dbPass + ';' +
            'Connect Timeout=15;';
        conn.Open(connectionString, conn);

The code used to submit the query looks like this:

    DB.query = function(query) {
    ...
            var cmd = Server.CreateObject("ADODB.Command");
            cmd.activeConnection = conn;
            cmd.commandText = query;
            for (var i = 1; i < arguments.length; i++) {
                cmd.Parameters(i-1).value = arguments[i];
            }
            return cmd.Execute();
    ...
    }

(Here, the parameters 1 and 56 are passed as additional arguments to DB.query.)

Answer 1

I see the query text and I see how the function accepts that text, but I'm still missing one thing to accurately diagnose this: how do you place the query text into your code?

I ask, because I have a feeling you have something like this:

query = "SELECT title, html, shortname FROM dbo.Videos"
      + "WHERE TopicID = ? .... ";

Notice what will happen when that string variable is created. It will look like this:

SELECT title, html, shortname FROM dbo.VideosWHERE TopicID = ? ....

Notice that there is nothing at all between dbo.Videos and WHERE. You did not include any whitespace between the two lines. The line break in your original code is not part of either string literal. You need something like this instead:

query = "SELECT title, html, shortname FROM dbo.Videos"
     + " WHERE TopicID = ? .... ";

Note the addition of a space for the WHERE keyword.

---

An additional suggestion is to drop the dbo. part and just use Videos for the table name. I know that what you have should be valid syntax, but something unusual must be going on or we'd have solved it already. One more thing to try is to place brackets around the table and column names.

Answer 2

I believe I've figured out the problem. Here's my hypothesis:

It seems that ordinarily ADO is able to infer the types of arguments based on the structure of the query. However, in this case one of the arguments to the prepared statement is a parameter for the function dbo.gradeLevelCheck, which causes the type inference to fail. (Presumably, ADO doesn't understand stored functions or something, so it doesn't know how to check what types the arguments should be.) Because it can't figure out the types, the query is invalid.

I still don't know why it is that this query appears to work if the ORDER BY clause is removed, but it's clear that I have to change the code anyway.

Answer 3

The "Incorrect syntax near the keyword 'from'" is a classical error you get when building your query by concatenating strings and forgetting some spaces between words. I guess here there is a space missing between the end of the first line (...videos) and the beginning of the second line (WHERE...)