accuracy vs generalization

Hi!

So sorry for posting yet another one of my silly posts about the correct way of doing things!

I am using Stored Procedures to do various things in my database (inserting/deleting/updating) and using SQL Server 2000 SP4

The current situation is that I have a stored procedure which checks certain user details and returns either -1 or 1 as a success indicator to the caller.

Now, obviously if we executed several queries in this stored procedure, performance will be an issue.

However, I have no idea how to balance up the whole "more useful information" vs "general information". What I mean is this:

if we are checking 3 items in a table, I want to return the success value back to the caller - in this case, we could do an IF statement to see if the record exists, if it does, set the return value to 1, else, -1.

But then you may want to be more informative to the user, specifically stating what part of the information they entered is incorrect/invalid. So having this in mind, we would then need to execute, say, 3 queries to return a more specific "error" value.

What should I do in this case?

I want to check the username, password and if the account is activated.

Currently I have this query going on:

IF EXISTS (SELECT [ID] FROM Users WHERE username = @.un AND [password] = @.pw AND activate = 1)

SET @.theResult = 1

ELSE

SET @.theResult = -1

firstly, is that the good way of doing things? if not - then what is the better way?

secondly, if we decide that we want a specific detailed return value/error message, it would mean I have to check the results I want using a couple more queries, such as one query to check username, one query to check password and another to check if the account is active or not and then return the appropriate value back to the caller.

is this a good way of doing things? I am confused and stuck!

Many thanks for your valuable response :)

>>Now, obviously if we executed several queries in this stored procedure, performance will be an issue.<<

This isn't necessarily true, but it is always better to minimize the number of queries.

Take this query:

IF EXISTS (SELECT [ID] FROM Users WHERE username = @.un AND [password] = @.pw AND activate = 1)

SET @.theResult = 1

ELSE

SET @.theResult = -1

A better way to write this might be:

select ID, case when activate = 0 then 'inactive' else 'active' end as activeStatus,
case when password = @.pw then 'correct' else 'incorrect' end as pwCheck
from users
where username = @.un

Then, the caller can interpret the details as they see fit:

No result set - invalid user name, the other two possibilities are obvious.

|||Many thanks!