Table-Valued Function using IF statement in SQL Server

Question

I have a Student table consists of following parameters

[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

i want to write a Table-Valued Function named Show which has one parameter as number. the function will act as following

• if @number = 1 , returns all columns from Student table
• if @number = 2 , returns only City from Student
• if @number = 3 , returns only Province from Student

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

 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)

Answer 1

This is not going to work:

INSERT INTO @result (City) 
VALUES ((SELECT City from Student))

Either you have all the values as scalar SQL variables, or literals - then you can use

INSERT INTO @result (City) 
VALUES ('New York')

INSERT INTO @result (City) 
VALUES (@ChosenCity)

or you have a SELECT statement to fill the values - then you need this syntax:

INSERT INTO @result (City) 
    SELECT City 
    FROM Student

without the VALUES keyword. And as @GiorgiNakeuri correctly states - this will then fail because all your columns require a value (have the NOT NULL attribute), so this insert cannot succeed - you need to provide all NOT NULL values (or define a default value for each column)