SQL Server: can you execute a stored procedure with an optional parameter? (See code)

Question

I basically want to either return just one patient that is entered through the parameter or return all patients if no value is passed in the parameter. Is this possible with my code? I keep getting this error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

My code:

create proc getOutsideLeadPatients 
     @patient int = null
as 
begin
    if @patient is not null 
       return (select * 
               from tblpatientdemographics p 
               where exists (select * from tblDBSSurgery s 
                             where s.idpatient = p.idpatient 
                               and s.blnoutside = 1 
                               and p.idpatient = @patient))
    else if @patient is null 
       return select * 
              from tblpatientdemographics p 
              where exists (select * from tblDBSSurgery s 
                            where s.idpatient = p.idpatient 
                              and s.blnoutside = 1)
end 
go 

Answer 1

Wow all I needed to do was get rid of the returns. Thanks Ben!

Answer 2

Here is a little trick:

create proc getOutsideLeadPatients 
     @patient int = null
as 
begin

select * 
               from tblpatientdemographics p 
               where exists (select 1 from tblDBSSurgery s 
                             where s.idpatient = p.idpatient 
                               and s.blnoutside = 1 
                               and p.idpatient = COALESCE(@patient, p.idpatient))


end 
go 

That is - if @patient is NULL, set it equal to p.idpatient. Then the condition will pass every time.

Answer 3

Perhaps try something along this line

SELECT p.* 
FROM tblpatientdemographics p 
INNER JOIN tblDBSSurgery s ON s.idpatient = p.idpatient
WHERE (ISNULL(@patient,0) = 0 OR p.idpatient = @patient)
AND s.blnoutside = 1

And as Ben mentioned above, you do not have to use Return to send data back.