CODEWITHSUNDEEP
sqlsql-server
Professed3376
Professed3376

Reputation: 411

SQL Server Stored Procedure - If Count( ) Statement

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

Upvotes: 2

Views: 16181

Answers (4)

Nicolas R
Nicolas R

Reputation: 14619

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

Upvotes: 2

sam yi
sam yi

Reputation: 4934

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)

Upvotes: 0

Vland
Vland

Reputation: 4272

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

Upvotes: 1

Ankush Madankar
Ankush Madankar

Reputation: 3834

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.

Upvotes: 0

Related Questions