I have an SQL query which does several things at once¹: 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?
¹ 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.
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.
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.
1