title: User defined function with CASE statement inside
body: <p>How can use case statement inside a T-SQL scalar function for the following code?
  (since dynamic SQL is NOT Allowed on T-SQL scalar functions)</p>
  
  <p>I want to switch / case on @Table</p>
  
  <pre><code>ALTER FUNCTION [dbo].[GetRowVersion] (@Id INT, @Table VARCHAR(20)) RETURNS BINARY(8) AS
  BEGIN
  DECLARE @rowVersion AS BINARY(8)
  IF @Table = 'Department' AND @Id = 0 SELECT @rowVersion =  MAX([RowVersion]) FROM Department
  IF @Table = 'Department' AND @Id &gt; 0 SELECT @rowVersion =  [RowVersion] FROM Department WHERE Id = @Id
  RETURN @rowVersion
  END
  GO
  </code></pre>
  
  <p>in example..</p>
  
  <p>begin case..</p>
  
  <p>case @Table = "Region".....</p>
  
  <pre><code>IF @Table = 'Department' AND @Id = 0 SELECT @rowVersion =  MAX([RowVersion]) FROM Department
  IF @Table = 'Department' AND @Id &gt; 0 SELECT @rowVersion =  [RowVersion] FROM Department WHERE Id = @Id
  RETURN @rowVersion
  </code></pre>
  
  <p>case @Table = "Deparment"....</p>
  
  <pre><code>IF @Table = 'Department' AND @Id = 0 SELECT @rowVersion =  MAX([RowVersion]) FROM Department
  IF @Table = 'Department' AND @Id &gt; 0 SELECT @rowVersion =  [RowVersion] FROM Department WHERE Id = @Id
  RETURN @rowVersion
  </code></pre>
  
  <p>...
  ...
  ... and so on ..
  case default....</p>
  
  <pre><code>RETURN @rowVersion
  </code></pre>
  
  <p>end case..</p>
  
  <p>I can't go beyond this point</p>
  
  <pre><code>CREATE FUNCTION [dbo].[GetRowVersion] (@Id INT, @Table VARCHAR(20)) RETURNS BINARY(8) AS
  BEGIN
  DECLARE @rowVersion AS BINARY(8)
     IF @Table = 'Department'
        BEGIN
           IF @Id = 0 SELECT @rowVersion = MAX([RowVersion]) FROM [Department]
           IF @Id &gt; 0 SELECT @rowVersion = [RowVersion] FROM [Department] WHERE Id = @Id
        END
     ELSE IF @Table = 'Address'
        BEGIN
           IF @Id = 0 SELECT @rowVersion = MAX([RowVersion]) FROM [Address]
           IF @Id &gt; 0 SELECT @rowVersion = [RowVersion] FROM [Address] WHERE Id = @Id
        END
     ELSE IF @Table = 'AddressType'
        BEGIN
           IF @Id = 0 SELECT @rowVersion = MAX([RowVersion]) FROM [AddressType]
           IF @Id &gt; 0 SELECT @rowVersion = [RowVersion] FROM [AddressType] WHERE Id = @Id
        END
     ELSE IF @Table = 'ContactType'
        BEGIN
           IF @Id = 0 SELECT @rowVersion = MAX([RowVersion]) FROM [ContactType]
           IF @Id &gt; 0 SELECT @rowVersion = [RowVersion] FROM [ContactType] WHERE Id = @Id
        END
     RETURN @rowVersion
  END
  GO
  </code></pre>
  
  <p>I got error:</p>
  
  <p>Msg 3701, Level 11, State 5, Line 5
  Cannot drop the function 'dbo.GetRowVersion', because it does not exist or you do not have permission.
  Msg 206, Level 16, State 2, Procedure GetRowVersion, Line 13 [Batch Start Line 13]
  Operand type clash: uniqueidentifier is incompatible with int</p>