Table-Valued Function using IF statement in SQL Server

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)

CREATE FUNCTION dbo.Show(    @number INT)RETURNS @result TABLE(    ID NVARCHAR(50),    Firsname NVARCHAR(50),    Lastname NVARCHAR(50),    Melicode NCHAR(10),    City NVARCHAR(50),    Province NVARCHAR(50),    Active INT)ASBEGIN    IF (@number = 1)        INSERT INTO @result        SELECT * FROM dbo.Student    IF (@number = 2)        INSERT INTO @result (City)        SELECT City FROM dbo.Student    IF (@number = 3)        INSERT INTO @result (Province)        SELECT Province FROM dbo.Student    RETURNENDGOSELECT * FROM dbo.Show(2)

the table returned is dictated by how the result table was declared. the query below works (in a sense) but the results include all the columns with NULLs for those columns not targeted by the @number parameter:

CREATE TABLE dbo.z_Show (str1 VARCHAR(10), str2 VARCHAR(10), str3 VARCHAR(10))INSERT z_showSELECT 1, 1, 1 UNION ALLSELECT 2, 2, 2 UNION ALLSELECT 3, 3, 3CREATE FUNCTION dbo.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    str1 VARCHAR(10), str2 VARCHAR(10), str3 VARCHAR(10)) ASBEGIN--for debugging|start--DECLARE @number INT = 3--DECLARE @result TABLE (str1 VARCHAR(10), str2 VARCHAR(10), str3 VARCHAR(10))--for debugging|end    IF  (@number = 1)    BEGIN           --PRINT ('IF (@number = 1)')         INSERT INTO @result SELECT * from dbo.z_Show    END    IF (@number = 2)    BEGIN        --PRINT ('IF (@number = 2)')         INSERT INTO @result (str2) SELECT str2 from dbo.z_Show    END     IF (@number = 3)     BEGIN           --PRINT ('IF (@number = 3)')         INSERT INTO @result (str3) SELECT str3 from dbo.z_Show     END    RETURN -- @Players (variable only required for Scalar functions)ENDSELECT 'number 1 was passed', *FROM dbo.show(1)SELECT 'number 2 was passed', *FROM dbo.show(2)SELECT 'number 3 was passed', *FROM dbo.show(3)