Sign up ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

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)
    if exists (select * from [People].[User] where [UserId] = @UserId and [IsLocked] = 0)
        if exists (select * from [People].[User]
                   where [UserId] = @UserId and [IsRemoved] = 0)
            update [People].[User] set [PasswordHash] = @Hash where [UserId] = @UserId
            select 0 -- Success.
            select 3 -- The account doesn't exist any longer.
        select 2 -- The account is locked.
    select 1 -- The user cannot be found.

The query is then used like this:

var errorCode = new SqlDataQuery(query, Program.ConnectionString)
    .With(new { UserId = this.Id, Hash = this.ComputeHash() })

// 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

2 Answers 2

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

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

Your Answer


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.