title: SQL Server stored procedure: disable "where" filter if related parameter is not set
body: <p>I got a SQL statement which uses some parameters to filter the output.</p>
  
  <p>Looking like this:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spGetPerson] 
          @Vorname varchar(50),
          @Nachname varchar(50),
          @FirmaID int,
          @AbteilungID int,
          @ArbeitsortID int,
          @FunktionID int
  AS
  BEGIN
      SET NOCOUNT ON;
  
      -- Insert statements for procedure here
      Select 
          p.Vorname, p.Nachname, ar.Arbeitsort, fi.Firma, 
          ab.Abteilung, fu.Funktion 
      From 
          tblPerson p
      inner join 
          tblArbeitsort ar on ar.ArbeitsortID = p.Arbeitsort_fk
      inner join 
          tblFirmaHatAbteilungUndPersonHatFunktion zt on zt.Person_fk = p.PersonID
      inner join 
          tblFirma fi on fi.FirmaID = zt.Firma_fk
      inner join 
          tblAbteilung ab on ab.AbteilungID = zt.Abteilung_fk
      inner join 
          tblFunktion fu on fu.FunktionID = zt.Funktion_fk
      Where 
          p.Vorname = @Vorname 
          AND p.Nachname = @Nachname 
          AND fi.FirmaID = @FirmaID 
          AND ab.AbteilungID = @AbteilungID 
          AND ar.ArbeitsortID = @ArbeitsortID 
          AND fu.FunktionID = @FunktionID
  END
  </code></pre>
  
  <p>I'm joining some stuff together but that's not important.</p>
  
  <p>As you can see at the end I use every parameter in my <code>WHERE</code> clause. What I would like to do is, if one parameter is missing the related "where" falls away. </p>
  
  <p><strong>As an example</strong> if <code>@Nachname</code> is missing the statement should look like this: </p>
  
  <pre><code>Where 
      p.Vorname = @Vorname 
      AND fi.FirmaID = @FirmaID 
      AND ab.AbteilungID = @AbteilungID 
      AND ar.ArbeitsortID = @ArbeitsortID 
      AND fu.FunktionID = @FunktionID
  </code></pre>
  
  <p>How do I do this?</p>