CODEWITHSUNDEEP
mysqlsql-server
Mahmudzadeh
Mahmudzadeh

Reputation: 3

inline table valued functions in sql server doesn't return data

I created a table [EMPLOYEE_DATEOFBIRTH] named 

CREATE TABLE EMPLOYEE_DATEOFBIRTH  (ID int primary key not null, 
NAME nvarchar(20) not null,
DATE_OF_BIRTH DATETIME2(7) not null, 
GENDER nvarchar(10) not null,
DEP_ID int not null)

and inserted data to this table.

INSERT INTO EMPLOYEE_DATEOFBIRTH  (ID, NAME, DATE_OF_BIRTH,GENDER,DEP_ID) VALUES (1,'Jack','1980-12-30 00:00:00.000','MALE',1)

INSERT INTO EMPLOYEE_DATEOFBIRTH (ID, NAME, DATE_OF_BIRTH,GENDER,DEP_ID) VALUES (2,'Ann','1982-09-01 12:02:36.260','FEMALE',2)

Then I created a function which returns list of employee by gender 

CREATE FUNCTION fnEmployeeByGender (@Gender nvarchar)
RETURNS TABLE
AS 
RETURN 
        (SELECT ID, NAME, DATE_OF_BIRTH,GENDER,DEP_ID 
        FROM EMPLOYEE_DATEOFBIRTH
        WHERE GENDER=@Gender)

to execute this function I use the query but it doesn't return anything 

SELECT * FROM fnEmployeeByGender('MALE')

what may be reason for this? thanks in advance.

Upvotes: 0

Views: 136

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15150

Your error is here: @Gender nvarchar. If you don't specify a length for an nvarchar, it defaults to one. So you are only searching for GENDER = 'M'. Solve it by giving a length: @Gender nvarchar(10).

Upvotes: 2

Related Questions