Using IF EXISTS with a stored procedure as argument

Question

I have a stored procedure called 'authenticate' that returns me the user profile record if provided correct username and password. Now I am trying to get all users in system if 'authenticate' stored procedure returned me a record otherwise return nothing. I am trying something like this:

IF EXISTS EXECUTE authenticate @UserName, @Password
BEGIN    
    SELECT * from Users;
END

I am getting error: Incorrect syntax near the keyword 'EXECUTE'. Any ideas what am I doing wrong?

Answer 1

You can transform your procedure into function:

create function dbo.authenticate(@UserName varchar(50), @Password varchar(50))
returns @found table(userName varchar(50), userPass varchar(50)) as
begin
  -- some of your internal table
  declare @user table (userName varchar(50), userPass varchar(50));
  insert into @user values ('John', '123'), ('Jack', '345');

  insert into @found (userName, userPass)
    select
      u.userName, u.userPass
    from
      @user u
    where
      u.userName = @UserName and u.userPass = @Password
  ;

  return;

end;

go

if exists(select * from dbo.authenticate('John', '123')) begin
  print 'exists';
end;

Answer 2

EXISTS takes a SELECT subquery not the results of a stored procedure. See MSDN. You will need to either replicate your SELECT from authenticate in your EXISTS statement or populate a table with the results prior to the EXISTS. e.g.

INSERT INTO #authenticate (col1, col2, col3)
EXEC authenticate @UserName, @Password

IF EXISTS (SELECT 1 FROM #authenticate)
BEGIN    
    SELECT * from Users;
END