title: Stored procedure always returns same query
body: <p>I tried to write a SQL query to get results with some parameters, but when I send parameters, it doesn't work. It always returns the last query in if else condition statement.</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE PROCEDURE [dbo].[sp_RealEstatesList]
     @TransCode NVARCHAR(5),
     @CatUrl NVARCHAR(255) = NULL,
     @ForSaleItem BIT = NULL,
     @NewItem BIT = NULL,
     @ItemOfDay BIT = NULL,
     @Random BIT = NULL,
     @Top INT = NULL
  AS
     SET NOCOUNT ON
     SET XACT_ABORT ON  
     SET FMTONLY OFF
  
     BEGIN TRAN
  
     IF (OBJECT_ID('tempdb..#TempTable') IS NOT NULL)
     BEGIN
         DROP TABLE #TempTable
     END
  
      SELECT 
          R.[ID],
          RT.[Title],
          R.[Price],
          R.[NewItem],
          R.[ForSaleItem],
          R.[ItemOfDay],
          R.[Url],
          (SELECT TOP 1 CC.Url
           FROM [dbo].[Category] CC
           JOIN [dbo].[LinkTypes] LLT ON LLT.[MainID]  = CC.ID
           JOIN [dbo].[Links] LL ON LL.[LinkTypeID] = LLT.[ID]
           JOIN [dbo].[RealEstates] RR  ON RR.[ID] = LL.LinkID
           WHERE LLT.[LinkedTypeID] = 17 
             AND LLT.[MainTypeID] = 1 
             AND RR.ID = R.[ID]) AS 'CatUrl',
          (SELECT TOP 1 
               (SELECT TOP 1 XC.Url 
                FROM Category XC
                JOIN [dbo].[LinkTypes] LLT ON LLT.[MainID]  = XC.ID
                JOIN [dbo].[Links] LL ON LL.[LinkTypeID] = LLT.[ID]
                WHERE LLT.[LinkedTypeID] = 1 
                  AND LLT.[MainTypeID] = 1 
                  AND LL.LinkID = CC.ID)
           FROM [dbo].[Category] CC
           JOIN [dbo].[LinkTypes] LLT ON LLT.[MainID]  = CC.ID
           JOIN [dbo].[Links] LL ON LL.[LinkTypeID] = LLT.[ID]
           JOIN [dbo].[RealEstates] RR  ON RR.[ID] = LL.LinkID
           WHERE LLT.[LinkedTypeID] = 17 
             AND LLT.[MainTypeID] = 1 
             AND RR.ID = R.[ID]) AS 'PCatUrl'
     INTO 
         #TempTable
     FROM 
         [dbo].[RealEstates] R 
     JOIN
         [dbo].[RealEstatesT] RT ON R.[ID] = RT.[RealEsID]
     JOIN
         [dbo].[LinkTypes] LT ON LT.[MainTypeID] = 17
     JOIN 
         [dbo].[Links] L ON L.[LinkTypeID] = LT.[ID]
     JOIN
         [dbo].[Translation] TR ON TR.[ID] = RT.[TransID]
     WHERE 
         R.[Active] = 1
         AND R.[Deleted] = 0
         AND RT.[Deleted] = 0
         AND LT.[LinkedTypeID] = 5 
         AND LT.[MainID]  = R.[ID]
         AND TR.[ShortName] = @TransCode
     GROUP BY
         R.[ID], RT.[Title], R.[Price], R.[NewItem], R.[ForSaleItem], R.[ItemOfDay], R.[Url]
  
  
     if (@Top = null or @Top = 0)
     begin
        set @Top = 100000;
     end
  
  
     if(@NewItem != null)
     begin
        select TOP (@Top) [ID],[Title],[Price],[NewItem],[ForSaleItem],[CatUrl],[PCatUrl],[ItemOfDay],[Url]
        from #TempTable
        where NewItem = @NewItem
        order by case when @Random = 1 then NEWID() end
     end
  
     else if(@ForSaleItem != null)
     begin
        select TOP (@Top) [ID],[Title],[Price],[NewItem],[ForSaleItem],[CatUrl],[PCatUrl],[ItemOfDay],[Url]
        from #TempTable
        where ForSaleItem = @ForSaleItem
        order by case when @Random = 1 then NEWID() end
     end
  
     else if(@CatUrl != null)
     begin
        select TOP (@Top) [ID],[Title],[Price],[NewItem],[ForSaleItem],[CatUrl],[PCatUrl],[ItemOfDay],[Url]
        from #TempTable
        where CatUrl = @CatUrl or PCatUrl = @CatUrl
        order by case when @Random = 1 then NEWID() end
     end
  
     else if(@ItemOfDay != null)
     begin
        select TOP (@Top) [ID],[Title],[Price],[NewItem],[ForSaleItem],[CatUrl],[PCatUrl],[ItemOfDay],[Url]
        from #TempTable
        where ItemOfDay = @ItemOfDay
        order by case when @Random = 1 then NEWID() end
     end
  
     else
     begin
        select TOP (@Top) [ID],[Title],[Price],[NewItem],[ForSaleItem],[CatUrl],[PCatUrl],[ItemOfDay],[Url]
        from #TempTable
        order by case when @Random = 1 then NEWID() end
     end
  
  COMMIT
  </code></pre>
  
  <p>Transcode and Random parameters work well but CatUrl, ForSaleItem, NewItem, ItemOfDay parameters does not work. It always returns the last query in if else condition statement.</p>
  
  <p>Hope you can help. I'm waiting for your answers.</p>
  
  <p><strong><em>edit :</em></strong> </p>
  
  <p>I changed all my null checks like "NULLIF(@Top, '') IS NULL" and now if statements works well.</p>