title: Table-Valued Function using IF statement in SQL Server
body: <p>I have a Student table consists of following parameters</p>
  
  <pre><code>[ID] [nvarchar](50) NOT NULL,
  [Firsname] [nvarchar](50) NOT NULL,
  [Lastname] [nvarchar](50) NOT NULL,
  [Melicode] [nchar](10) NOT NULL,
  [City] [nvarchar](50) NOT NULL,
  [Province] [nvarchar](50) NOT NULL,
  [Active] [int] NULL
  </code></pre>
  
  <p>i want to write a Table-Valued Function named Show which has one parameter as number. the function will act as following</p>
  
  <ul>
  <li>if @number = 1 , returns all columns from Student table</li>
  <li>if @number = 2 , returns only City from Student</li>
  <li>if @number = 3 , returns only Province from Student</li>
  </ul>
  
  <p>i wrote the following T-SQL, but it only works for (if (@number = 1)). When the user enter @number as 2 or 3, the function does not work. Thank You</p>
  
  <pre><code> Create function Show(@number int)
  RETURNS @result TABLE
  (
      [ID] [nvarchar](50) NOT NULL,
      [Firsname] [nvarchar](50) NOT NULL,
      [Lastname] [nvarchar](50) NOT NULL,
      [Melicode] [nchar](10) NOT NULL,
      [City] [nvarchar](50) NOT NULL,
      [Province] [nvarchar](50) NOT NULL,
      [Active] [int] NULL
  ) 
  AS
  BEGIN
  
      IF  (@number = 1)
           INSERT INTO @result SELECT * from Student 
  
      IF (@number = 2)
           INSERT INTO @result (City) values ((SELECT City from Student))
  
       IF (@number = 3)
           INSERT INTO @result (Province) values ((SELECT Province from Student))
  
      RETURN -- @Players (variable only required for Scalar functions)
  
  END
  
  go
  select *from dbo.show(1)
  </code></pre>