SqlServer: Conversion failed when converting from a character string to uniqueidentifier

Question

User.where('user_id not in (?)', CancelledUser.all.collect(&:id).join(', '))

Above query gives me the following error when there are no cancelled users.

ActiveRecord::StatementInvalid: TinyTds::Error: Conversion failed when converting from a character string to uniqueidentifier.: EXEC sp_executesql N'SELECT [users].* FROM [userss] WHERE (user_id not in (N''''))'

How do i fix this?

Answer 1

You don't want to join the ids, that's going to be treated as a single string literal. Try the following:

User.where('user_id not in (?)', CancelledUser.all.collect(&:id))

Update:

Executing the generated query in SQL Server directly also yields the same problem. Take a look at the answer in Issue when retrieving records with empty array for same problem when you have empty array.

So you could do the following to solve the issue:

cancelled_users = CancelledUser.all.collect(&:id)

if cancelled_users.any?
  User.where('user_id not in (?)', cancelled_users)
else
  User.all
end