Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
"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. –  MainMa Apr 30 '13 at 19:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.