SQL: StoredProcedure Return value

MargeS

I have a stored Procedure that checks for data already in the DB.

My form has an Email and Username TextField.
My SP takes the email and username and makes sure that these values are not already in the DB table. This ensures that emails and usernames are not duplicated.
If the SP returns a value of <> 0 then the user is told that these values are already used ELSE the data is inserted.

I can get all this to work perfectly BUT my SP returns a 0/1 value regardless of whether the email OR username are duplicates.

How do I know which one failed - Email or Username and pass this info back to user

See below:

CREATE PROCEDURE dbo.sp_CheckDup
(
@Email varchar (128),
@Username varchar (128)
)
AS
BEGIN

-- Remove any leading/trailing spaces from parameters
SELECT @Email = LTRIM(RTRIM(@Email))
SELECT @Username = LTRIM(RTRIM(@Username))

BEGIN
SELECT * from tblUsers1 WHERE Email = @Email OR Username = @Username
@ROWCOUNT = 1
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
END

END
GO

Evil Phil

 CREATE PROCEDURE dbo.sp_CheckDup
(
@Email varchar (128),
@Username varchar (128)
)
AS
BEGIN

-- Remove any leading/trailing spaces from parameters
SELECT @Email = LTRIM(RTRIM(@Email))
SELECT @Username = LTRIM(RTRIM(@Username))

DECLARE @RetVal int
SELECT * from tblUsers1 WHERE Email = @Email 
if @@ROWCOUNT = 1
BEGIN
SET @RevVal = 1
END

SELECT * from tblUsers1 WHERE Username = @Username
If @@ROWCOUNT = 1
BEGIN
SET @RetVal = @RetVal + 2
END
END
GO

Just of the top of my head you understand so the code may not actually execute due to errors. Basically what it does is if the email value is present it returns 1, if the username value is present it returns 2 and if both are present it returns 3. It's just one way of doing it - a good exercise for yourself would be to try find another way of doing it.

amen

Evil Phil, that does work but why hit the table multiple times? Personnaly I would just check and once I got a false exit the sp much nicer and also less db resource hungry

Ginger

First off...

Your rowcount statement is incrorrect because it can return more than 1 row.. if you have a username and an email that matches.. So you should check your rowcount greater than 0!

Next .. dont use select * because why do you need to return the whole datarow if you are only return true or false????

OK onto solving this

Your sp checks if either the email or username exists in the table

Declare @MaxCount TINYINT

SELECT @MaxCount = ISNULL(COUNT(PK),0) FROM tblUsers1 WHERE Username=@USERNAME or Email=@EMAIL

RETURN @MaxCount

works but doesnt tell you what one is your duplicate does it???

So you need 2 hits to the db as Evil Phil suggested...

YOu can a couple of things some of which would not be recommended .. being honest, 2 hits to the db is your easiet.. just make sure you indexes are good and it will be quick and return you the correct info..

mewso

Something like:

if (select count(email) from tblusers1) > 0
   begin
      return 1
   end
elseif (select count(username) from tblusers1) > 0
   begin
      return 2
   end
else
   begin
      return 0
   end

will only need 2 hits at the db if the email isn't found. Not sure if I have the syntax right there though.

bonkey

amen wrote:

Evil Phil, that does work but why hit the table multiple times?

You have two tests to perform :
- Does the username exist
- Does the email exist

The easiest way to do this is to hit the table twice.

In theory, a better way should be to write a more complex SQL statement which gives you both answers which you can test afterwards. Unfortunately, this makes the single SQL statement more resource-hungry than either of the single statements described above.

In theory, if both username and email were indexed, it may actually be more efficient to run two seperate queries than one.

It can certainly be done in one, but realistically you're going to have to do something like :

SELECT @UsernameCount = SUM( CASE 
                                 WHEN username = @username 
                                   THEN 1 
                                 ELSE 0 
                               END)
    ,  @EmailCount    = SUM( CASE 
                                 WHEN email = @email 
                                   THEN 1 
                                 ELSE 0 
                               END)
FROM   tblUsers
WHERE  username = @Username
  OR   email = @email

So, one query, populating two variables, which you can check afterwards to see if you got results.
The tradeoff is that each found record requires two CASE evaluations, and the query also requires two SUMs.

Is it more efficient? Maybe.

Ginger

For such a small amount of data to be returned its probably overkill. But since its compiled code due to it being an sp you could probably get away with it.

Depending on the other factors on how many times this was going to work etc.. it may not require such complexity.

POssibly a simpler way is to use an sp that can accept a field name to search against and return true or false and then allow the coder decide what level to go into... Then its at the business level...

All depends on what the app will be used for i guess...

Evil Phil

amen wrote:

Evil Phil, that does work but why hit the table multiple times? Personnaly I would just check and once I got a false exit the sp much nicer and also less db resource hungry

As I said it was just off the top of my head but he does need to know which value failed. It's just one way of doing it, there's far better ways of doing it true but that's the op's job, not mine

bonkey

Evil Phil wrote:

there's far better ways of doing it

I'm not convinced there are, but anyway....

One should always remember the "golden rule" :

1) Make it work as required
2) Optimise if needed.

Evil Phil

I confess my Sql is not great I'm a front end boy these days.

MargeS

thanks everyone - if there is one thing I have learned it's:

if there is one way to do it there are a least 5 other ways to do it!

MargeS

musician wrote:

Something like:

if (select count(email) from tblusers1) > 0
   begin
      return 1
   end
elseif (select count(username) from tblusers1) > 0
   begin
      return 2
   end
else
   begin
      return 0
   end

will only need 2 hits at the db if the email isn't found. Not sure if I have the syntax right there though.

@rowcount is used because regardless of one row being returned or multilple rows it still means that the username or email address are already in the DB so they cannot be used?

Ginger

@ROWCOUNT is the number of rows returned by the previous statement.

Count() returns the number of rows that match your criteria as a value..

@ROWCOUNT would return 1 for the statment, because only 1 row with one column in this case is returned.

ceejay

Since it appears that a particular email address can appear only once in the table, and a particular username can also appear only once, then the query to see if a match is found should always return 0 or 1 rows. Therefore you should be able to store the actual username and email returned from the table into two variables, and then you can use and IF statement to check the found values with the input values and return an appropriate value from the SP, but with only one SELECT.

Ciarán.

Ginger

You could also put a trigger on the table ..

Ceejay.. the query is that the username or the email cannot appear therefore you can use the username and email once.. best case 1 row because they are contained in the same, worst case 2 rows because 1 row has the email and the other row has the username (hence the OR query in the original post). This is why you need 2 selects...

You could also add a UNIQUE constraint to the particular fields thus negating the need to check because they will throw back an error..

bonkey

ceejay wrote:

Since it appears that a particular email address can appear only once in the table, and a particular username can also appear only once, then the query to see if a match is found should always return 0 or 1 rows.

Consider the following :

We have two records in th2 DB. Record 1 has UserName1, Email1. Record 2 has UserName2, Email2.

We search for Username1, Email2. How many rows will be returned?

If you perform each search seperately, then sure, it should work like you say.

However, even then, unless there was a Unique Index ensuring that there could never be more than one of each value, I would argue it is a dangerous practice to assume data consistency.

but with only one SELECT.

With only one SELECT you can get 2 rows returned, requiring the use of grouping functions....as per my sample earlier.

Ginger

Consider bonkey's example above

Username1 AND Email2 would return 0 rows

Username1 OR Email2 would return 2 rows

So if you use the AND query you could put in multiple instances of the username and email until someone put in both in the same row because your check would always return 0.

Using the OR is sufficient but it doesnt tell you which one criteria matched.. especially if 1 row is returned.. 2 well means both are taken, 1 means either..

ceejay

bonkey wrote:

Consider the following :

We have two records in th2 DB. Record 1 has UserName1, Email1. Record 2 has UserName2, Email2.

We search for Username1, Email2. How many rows will be returned?

Yep, I wasn't thinking straight

Ciarán.