title: IF EXISTS in WHERE clause
body: <p>I ran into a problem this days. In my MS SQL Database I have a table of articles (details of them like insert date, insert user and other staffs) and one table with the body of articles in multiple languages. I want that the articles body to be in user preferred language. But not all the articles are in all languages. So, would be nice, first to search for the article in user's preferred language and, if not exists, to get the body in first language it is.</p>
  
  <p>For this, I use a function. The WHERE clause is like this: </p>
  
  <pre><code>WHERE   [tblBody].[Language] = @Language AND
          [tblTitle].[Language] = @Language
  </code></pre>
  
  <p>but would be nice if it would be like this:
  SELECT (if body is in my preferred language get that body; else give me one (maybe I understood it))</p>
  
  <p>I hope you understood what I want to do and witch are my problems.</p>
  
  <p>Thank you in advance!</p>
  
  <p>UPDATE</p>
  
  <p>This is my actual query which needs modifications:</p>
  
  <pre><code> ALTER FUNCTION [fx_GetNews]
  (   
      @MinimumPermission INT,
      @UserID UNIQUEIDENTIFIER,
      @OwnerID  UNIQUEIDENTIFIER = NULL,
      @Title NVARCHAR(250) = NULL,
      @Body VARCHAR(150) = NULL,
      @InsertDateStart DATETIME = NULL,
      @InsertDateEnd DATETIME = NULL,
      @CreatedByUserID UNIQUEIDENTIFIER = NULL,
      @ExpirationDate DATETIME = NULL,
      @Language VARCHAR(150)
  )
  RETURNS @News TABLE 
  (
      [Id] UNIQUEIDENTIFIER
      ,[OwnerID] UNIQUEIDENTIFIER
      ,[Title] NVARCHAR(250)
      ,[TitlePictureUrl] VARCHAR(150)
      ,[Body] NVARCHAR(max)
      ,[Visible] BIT
      ,[InsertDate] DATETIME
      ,[CreatedByUserId] UNIQUEIDENTIFIER
      ,[ModifiedDate] DATETIME
      ,[ModifiedByUserId] UNIQUEIDENTIFIER
      ,[ModifiedByPerson] VARCHAR(250)
      ,[ExpirationDate] DATETIME
      ,[CreatedByPerson] VARCHAR(250)
      ,[Permission] INT
  )
  AS
  BEGIN
      INSERT INTO @News
             ([Id]
             ,[OwnerID]
             ,[Title]
             ,[TitlePictureUrl]
             ,[Body]
             ,[Visible]
             ,[InsertDate]
             ,[CreatedByUserId]
             ,[ModifiedDate]
             ,[ModifiedByUserId]
             ,[CreatedByPerson]
             ,[ModifiedByPerson]
             ,[ExpirationDate]
             ,[Permission])
          SELECT
                  [dbo].[tblNews].[Id]
                 ,[dbo].[tblNews].[OwnerID]
                 ,CAST([tblTitle].[Text] AS VARCHAR(150))
                 ,[dbo].[tblNews].[TitlePictureUrl]
                 ,[tblBody].[Text]
                 ,[dbo].[tblNews].[Visible]
                 ,[dbo].[tblNews].[InsertDate]
                 ,[dbo].[tblNews].[CreatedByUserId]
                 ,[dbo].[tblNews].[ModifiedDate]
                 ,[dbo].[tblNews].[ModifiedByUserId]
                 ,[dbo].[tblNews].[CreatedByPerson]
                 ,[dbo].[tblNews].[ModifiedByPerson]
                 ,[dbo].[tblNews].[ExpirationDate]
                 ,[eportofolii].[fx_GetPermissionForObject] (@UserID, [dbo].[tblNews].[ID], 1, 1)
             FROM [dbo].[tblNews] 
                      INNER JOIN [dbo].[tblAdmTranslateText] AS [tblBody] ON
                          [tblBody].[OwnerID] = [dbo].[tblNews].[ID]
                      INNER JOIN [dbo].[tblAdmTranslateText] AS [tblTitle] ON
                          [tblTitle].[OwnerID] = [dbo].[tblNews].[ID]
          WHERE
              [eportofolii].[fx_GetPermissionForObject] (@UserID, [dbo].[tblNews].[ID], 1, 1) &gt;= @MinimumPermission AND
              ([dbo].[tblNews].[OwnerID] = ISNULL(@OwnerID, [dbo].[tblNews].[OwnerID]) OR [dbo].[tblNews].[OwnerID] IS NULL) AND
              [tblTitle].[Text] LIKE '%' + ISNULL(@Title, '') + '%' AND
              [tblBody].[Text] LIKE '%' + ISNULL(@Body, '') + '%' AND
              ([dbo].[tblNews].[InsertDate] BETWEEN ISNULL(@InsertDateStart, ([dbo].[tblNews].[InsertDate] - 7)) AND ISNULL(@InsertDateEnd, [dbo].[tblNews].[InsertDate] + 1)) AND
              [dbo].[tblNews].[CreatedByUserID] = ISNULL(@CreatedByUserID, [dbo].[tblNews].[CreatedByUserID]) AND
              ([dbo].[tblNews].[ExpirationDate] &gt; ISNULL(@ExpirationDate, GETDATE() - 1) OR [dbo].[tblNews].[ExpirationDate] IS NULL) AND
              [tblBody].[UDF_2] = 'Body' AND 
              [tblTitle].[UDF_2] = 'Title' AND 
              [tblBody].[UDF_1] = 'News' AND 
              [tblTitle].[UDF_1] = 'News' AND
              [tblBody].[Language] = @Language AND
              [tblTitle].[Language] = @Language
          ORDER BY [InsertDate] DESC
      RETURN
  END
  </code></pre>
  
  <p>The problem is here at the end:</p>
  
  <pre><code>[tblBody].[Language] = @Language AND
  [tblTitle].[Language] = @Language
  </code></pre>
  
  <p>Thank you!</p>