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)

Doesn't work? Can't believe.

Giorgi Nakeuri
– Giorgi Nakeuri

2015-11-26 09:21:36 +00:00
Commented Nov 26, 2015 at 9:21 — Giorgi Nakeuri
– Giorgi Nakeuri, Commented Nov 26, 2015 at 9:21

marc_s · Accepted Answer · 2015-11-26 09:32:06Z

11

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)

edited Nov 26, 2015 at 9:32

answered Nov 26, 2015 at 9:23

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Giorgi Nakeuri Over a year ago

You missed that all columns are not nullable.

Ramon Fonis Over a year ago

@marc_s i want the ID to be not nullable. it doesn't sound logic in my database to put it nullable, but function wouldn't work with that. i have no idea how to fix it.

marc_s Over a year ago

@user3187561: well, then you have to make all other columns be NULL instead of NOT NULL, and you have to find a way to provide a value for ID even in the case of providing only city or province values

Devart · Accepted Answer · 2015-11-26 09:28:02Z

2

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
)
AS
BEGIN

    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

    RETURN

END
GO

SELECT * FROM dbo.Show(2)

edited Nov 26, 2015 at 9:28

answered Nov 26, 2015 at 9:23

Devart

120k23 gold badges172 silver badges189 bronze badges

3 Comments

Devart Over a year ago

@Giorgi Nakeuri, maybe you want tell what's wrong in this answer? Two words is missing? :)

Giorgi Nakeuri Over a year ago

Yes two words are missing. Also it wont work because all columns in table are not nullable, and noone noticed that.

Ramon Fonis Over a year ago

@Giorgi Nakeuri - Thank you, the not nullable values are another problem. i want the ID to be not nullable. but the function wouldn't work with that.

t1t1an0 · Accepted Answer · 2015-11-26 09:42:43Z

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_show
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2 UNION ALL
SELECT 3, 3, 3

CREATE 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)
) 
AS
BEGIN
--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)
END

SELECT '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)

marc_s · Accepted Answer · 2015-11-26 09:59:22Z

2

You mentioned @result has all NOT NULL columns. If you want to insert only city into that @result, it will take remaining columns as Null so that's why an error happened. You don't mention that @result columns are NOT NULL columns and one more is. Remove VALUES keyword from the INSERT statement because it is inserting with a Select statement

edited Nov 26, 2015 at 9:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 26, 2015 at 9:48

Aj.na

2832 silver badges9 bronze badges

3 Comments

Ramon Fonis Over a year ago

Yes, it helps. But the problem is the Result is provided with a table which only one column has data and all other columns have the NULL value which doesn't look good.

Aj.na Over a year ago

@user3187561 do you want to replace these null values with any other values?

Ramon Fonis Over a year ago

No, i just wanted the NULL value to be hidden in the result. for example if i read the @number = 2 from user, the result should be just City column with value, not other columns with NULL.

Eduard Uta · Accepted Answer · 2015-11-26 09:22:43Z

1

The insert statements for cases 2 and 3 are incorrect. No need for VALUES keyword when inserting values coming from a select statement.

answered Nov 26, 2015 at 9:22

Eduard Uta

2,6165 gold badges30 silver badges39 bronze badges

2 Comments

Ramon Fonis Over a year ago

i delete the VALUES keyword. so it becomes like : IF (@number = 2) INSERT INTO @result (City) (SELECT City from Student) but, still doesn't work.

Eduard Uta Over a year ago

Of course you don't need the ( and ) paranthesis - these should be removed as well.

Collectives™ on Stack Overflow

Table-Valued Function using IF statement in SQL Server

5 Answers 5

3 Comments

3 Comments

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related