Are error codes wrong in SQL queries too?

Question

I have an SQL query which does several things at once1: it does multiple checks and then, if the checks pass, updates some data. Without entering in the domain-specific details, here's an imaginary example:

-- The user password should be updated only if the user account is not removed and
-- not locked, and the threshold was not exceeded.
if exists (select * from [People].[User] where [UserId] = @UserId)
begin
 if exists (select * from [People].[User] where [UserId] = @UserId and [IsLocked] = 0)
 begin
 if exists (select * from [People].[User]
 where [UserId] = @UserId and [IsRemoved] = 0)
 begin
 update [People].[User] set [PasswordHash] = @Hash where [UserId] = @UserId
 select 0 -- Success.
 end
 else
 begin
 select 3 -- The account doesn't exist any longer.
 end
 end
 else
 begin
 select 2 -- The account is locked.
 end
end
else
begin
 select 1 -- The user cannot be found.
end

The query is then used like this:

var errorCode = new SqlDataQuery(query, Program.ConnectionString)
 .With(new { UserId = this.Id, Hash = this.ComputeHash() })
 .ReadValue<int>();
// Error codes are used only between the database and code, and transformed into exceptions
// as soon as possible.
switch (errorCode)
{
 case 1: throw new UserNotFoundException();
 case 2: throw new AccountPermissionException();
 case 3: throw new AccountRemovedException();
}

In general programming, error codes are considered bad and should be replaced by conventional error handling procedures, including exceptions.

Is it also bad to use them in SQL queries? If yes, how should I rewrite the query to follow best practices?

---

^{1 Given the non-functional requirements related to the load and performance, there is no way to make several consecutive queries. All the checks and the update should be done in a single query.}

Answer 1

Depending on your SQL implementation, you may have a RAISE, RAISERROR, or similar statement you can use in your query to fail the operation with an exception-like result. That's the best answer.

Answer 2

Instead of relying on the control-of-flow logic in your database, why not just get the data and handle it in your app code?

select userid, islocked, isremoved from [People].[User] where [UserId] = @UserId

Based on whether or not a record is returned or the values for IsLocked and IsRemvoed, you can update the data or generate errors.

I don't know about other RDBS, but SQL Server has Try/Catch blocks that can also handle various types of errors.

• Arseni Mourzenko: "why not just get the data and handle it in your app code?": Because this would make two queries instead of one. As I said in my question, the non-functional requirements are extremely strict, so this is not a possibility.