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
– Gordon Linoff, Commented Dec 26, 2013 at 16:17
What do you mean an Index? Like a column named "Id" which would be an int and auto incremement? — Professed3376
– Professed3376, Commented Dec 26, 2013 at 16:27

Raj Baral · Accepted Answer · 2018-02-26 19:46:59Z

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, 2018 at 19:46

Raj Baral

7018 silver badges19 bronze badges

answered Dec 26, 2013 at 16:13

Nicolas R

14.7k2 gold badges32 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vland · Accepted Answer · 2013-12-26 16:22:41Z

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, 2013 at 16:22

answered Dec 26, 2013 at 16:08

Vland

4,2822 gold badges36 silver badges46 bronze badges

1 Comment

Professed3376 Over a year ago

Could be useful, See my edit above for what I ended up using.

marc_s · Accepted Answer · 2013-12-26 16:20:14Z

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, 2013 at 16:20

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 26, 2013 at 16:07

Ankush Madankar

3,8724 gold badges46 silver badges78 bronze badges

2 Comments

Professed3376 Over a year ago

Seems to still make "2" calls to the DB which I don't want

sam yi Over a year ago

select * and select 1 makes no difference. stackoverflow.com/questions/1221559/count-vs-count1

sam yi · Accepted Answer · 2013-12-26 16:39:31Z

0

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)

edited Dec 26, 2013 at 16:39

answered Dec 26, 2013 at 16:27

sam yi

4,9411 gold badge33 silver badges41 bronze badges

1 Comment

Professed3376 Over a year ago

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

Collectives™ on Stack Overflow

SQL Server Stored Procedure - If Count( ) Statement

4 Answers 4

Comments

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related