SQL Server Stored Procedure - If Count( ) Statement

Question

I am writing a stored procedure for SQL Server. I have a working version but I'm looking to optimize it a little bit if possible so that I don't need to waste two calls to the database.

Here is my version :

If Exists (Select * From webapi.dbo.Users Where Username=@Username)
Begin
    Select * From webapi.dbo.Users Where Username=@Username
End
Else
Begin
    Set @error_message = 'Username ' + @Username + ' does not exist.'
    Raiserror (@error_message, 16, 1)
End

I want something like :

Set @User = (Select * From webapi.dbo.Users Where Username=@Username)
If Count(@User) =
    Begin
        Set @error_message = 'Username ' + @Username + ' does not exist.'
        Raiserror (@error_message, 16, 1)
    End
Else
    Begin
        return @User
    End

Is this possible or a smart thing to do? I clearly make 2 of the same calls and would like to remove one of them in order to create the fastest possible system and for my own knowledge of SQL.

I Ended up using:

Select * From webapi.dbo.Users Where Username=@Username
If @@ROWCOUNT = 0
    Begin
        Set @error_message = 'Username ' + @Username + ' does not exist.'
        Raiserror (@error_message, 16, 1)       
    End

If you have an index on `webapi.dbo.users(username)` the calls will be very fast. You would gain very little in performance by trying to eliminate one of them. — Gordon Linoff, Dec 26 '13 at 16:17
What do you mean an Index? Like a column named "Id" which would be an int and auto incremement? — Professed3376, Dec 26 '13 at 16:27

score 2 · Accepted Answer · edited Feb 26 '18 at 19:46

2

You cannot store your select result in a classical variable because it's a row, not a field.

For your information if you only need to get 1 field, you could do what you thought with @@ROWCOUNT:

DECLARE @var INT; 

SELECT @var = id FROM tableName WHERE condition...;

After this select ask for @@ROWCOUNT

IF @@ROWCOUNT = 0 BEGIN 
RAISERROR... 
END

edited Feb 26 '18 at 19:46

Raj Baral

661
6
19

answered Dec 26 '13 at 16:13

Nicolas R

13,812
2
28
57

Vland · Answer 2 · 2013-12-26T16:22:41.940

1

What about something like this?

declare @Username varchar(50) = 'johnny'

if not exists (select 1 from users where username = @username) 
raiserror ('%s does not exist',16,1,@username)

T-SQL example

edited Dec 26 '13 at 16:22

answered Dec 26 '13 at 16:08

Vland

4,151
2
32
43

Could be useful, See my edit above for what I ended up using. – Professed3376 Dec 26 '13 at 16:21

score 0 · Answer 3 · edited Dec 26 '13 at 16:20

0

Not exactly what you want but little optimize than yours :

 If Exists (Select 1 From webapi.dbo.Users Where Username=@Username)
    Begin
        Set @error_message = 'Username ' + @Username + ' does not exist.'
        Raiserror (@error_message, 16, 1)
    End
Else
    Begin
        Select * From webapi.dbo.Users Where Username=@Username
    End

Instead of using * you can use simply 1 to check condition.

edited Dec 26 '13 at 16:20

marc_s

732,580
175
1,330
1,459

answered Dec 26 '13 at 16:07

Ankush Madankar

3,689
4
40
74

Seems to still make "2" calls to the DB which I don't want – Professed3376 Dec 26 '13 at 16:22
1

select * and select 1 makes no difference. http://stackoverflow.com/questions/1221559/count-vs-count1 – sam yi Dec 26 '13 at 16:22

sam yi · Answer 4 · 2013-12-26T16:39:31.970

How come you need to "raiserror" in the database if you're concerned about your round trips. Just hit the database for UserId or UserName

select @userid = userid from users where username = @username

and if that variable comes back null, just raise that error at the application tier....

I'm actually not sure what you mean by "two calls" to the database. The solution you've decided to go with uses "two calls" just the same. I think there's a bit of confusion here. In your definition of "call", your "if @@rowcount" would be considered a call.

Why not just

if not exists (select * from user where username = @username)
  raiserror('no user',16,1)

Is it not good practice to raiserror's? I just thought it would be easier to raise it in the database level, but I can do it in the database access layer too.. — Professed3376, Dec 26 '13 at 16:28

SQL Server Stored Procedure - If Count( ) Statement

4 Answers4