Wrong query: Incorrect syntax near ')'

Question

Wrong query: Incorrect syntax near ')'

asp.net dapper sql-server-2008

Question

I have ASP.NET application and we use Dapper library. The code that produces the error looks as following:

public bool CheckIfExists(IEnumerable<long> ticketGroups, long dateId, int userId)
{
    bool bRetVal = false;
    string sql = "if exists (select * from T_TicketGroupsToChangePrice where SubTypeId = @SubTypeId and DateId = @dateId and UserId = @userId)";
    using (var conn = CreateSqlConnection())
    try
    {
        int rows = conn.Execute(sql, ticketGroups.Select(g => new { SubTypeId = g, UserId = userId, dateId }));
        if (rows > 0)
            bRetVal = true;
    }
    catch (SqlException ex)
    {
        throw new Exception("Error", ex);
    }

    return bRetVal;
}

When I run the application it throws the exeption: Incorrect syntax near ')'

As you can see, there can be more tickets (IEnumerable type) with the same date and user.

I'm not sure what's going on.

tesicg

Popular Answer

If your result depends on the number of rows and not on what's returned from the SQL, you could try this:

if exists ([whatever]) select 1

This works, because if there are no matching values, no recordset is returned, and your affected record count is zero.

You could also try something a bit simpler:

select 1 
from T_TicketGroupsToChangePrice 
where SubTypeId = @SubTypeId 
  and DateId = @dateId 
  and UserId = @userId;

But that has the disadvantage of returning one row for however many records you have. This could be a lot, depending on the app and the context, and in any case you don't want to pull over data that you're not going to use.

I wouldn't recommend a CASE statement, because SELECT CASE EXISTS ([whatever]) THEN 1 END will still return one record, and your affected record count will be 1 even if no records exist.

The problem with your original SQL, by the way: The statement is incomplete. You're saying "if exists ..." but you never finish it with the equivalent of a "then". You need to say "if exists() select 1" or something similar.

Ann L.

View more on Stack Overflow

Patrick Hofman · Accepted Answer · 7/17/2015 10:11:10 AM

That is because it is not valid SQL to start with an if (If you mean to use T-SQL it is, but then you have to write the entire if statement)

I think a simple case is what you need:

select case
       when exists (select * from T_TicketGroupsToChangePrice where SubTypeId = @SubTypeId and DateId = @dateId and UserId = @userId)
       then 1
       else 0
       end

Wrong query: Incorrect syntax near ')'

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator