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.
Accepted Answer
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
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.