How to fix "Incorrect syntax near ')'". when there is no ) in the query

user_7221508 0
Viewed 207

I am attempting to run a query against my database using Dapper, however, an exception is being thrown. The message of the exception is

Incorrect syntax near ')'

The part of this that is confusing to me is that I have no right parens in my entire query. It used to have some, but I have been simplifying it until I got to this:

select i.id as EmployeeReviewId
from @ReviewIds i

Which still causes the error.

@ReviewIds is a table-valued-parameter that only has one column, an int column named id.

I am calling the function like this:

await connection.QueryAsync<FooInfo>(sql, new { reviewIds });

where FooInfo is an object that has an EmployeeReviewId property, and reviewIds is an enumerable of int.

I have to assume that this is something to do with how I am using Dapper.

Would anyone know how to fix this issue?

dapper sql c#
1 Answers

You are probably passing user defined table parameter incorrectly. You have to pass it as DataTable, not as a simple array. A minimal working example is

public class FooInfo
{
    public int EmployeeReviewId { get; set; }
}

class Program
{
    static async Task Main(string[] args)
    {
        using (var connection =
            new SqlConnection("Persist Security Info=False;Integrated Security=true;Initial Catalog=YOUR_DB_NAME;Server=."))
        {
            var result = await connection.QueryAsync<FooInfo>("select i.id as EmployeeReviewId from @ReviewIds i", new
            {
                ReviewIds = CreateTableType(new[] {1, 2, 3})
            });
            Console.WriteLine(result);
        }
    }

    private static DataTable CreateTableType(IEnumerable<int> nums)
    {
        var t = new DataTable();
        t.SetTypeName("dbo.ArrayOfInt");
        t.Columns.Add("id");
        foreach (var num in nums)
        {
            t.Rows.Add(num);
        }
        return t;
    }
}

Where dbo.ArrayOfInt is

CREATE TYPE dbo.ArrayOfInt AS TABLE  
(  
id int NOT NULL
)
user_5174460
Related