multi-statement table valued UDF

[mansi sharma]

Three types of Functions are there-
1)Simple Scalar function-It returns a scalar value.

2)Inline-Table Valued UDF-
It allows us to return the table. Am I right or wrong??

3) Multi-statement table valued UDF-
Can somebody tell me the definition of it.

create table studentp(roll int,name varchar,marks int,address varchar(10))
insert into studentp values(1,'A',10,'FBD')
insert into studentp values(2,'B',20,'Delhi')
insert into studentp values(3,'C',30,'Gurgaon')
select * from studentp

create function info2(@roll int)Returns @myTable TABLE
(
roll int,
name varchar,
address varchar(10)
)
AS
BEGIN
INSERT @myTable
Select roll,name,address
FROM studentp
RETURN
END

SELECT * FROM dbo.info2(1)

When I m calling the function it shows all the rows of the table,but i m passing roll as parameter to the function & i m passing roll Number 1,function has to showed the result of only Roll Number 1 ,then why all the rows are returned.

[siva538]

Three types of Functions are there-
1)Simple Scalar function-It returns a scalar value.

2)Inline-Table Valued UDF-
It allows us to return the table. Am I right or wrong??

3) Multi-statement table valued UDF-
Can somebody tell me the definition of it.

create table studentp(roll int,name varchar,marks int,address varchar(10))
insert into studentp values(1,'A',10,'FBD')
insert into studentp values(2,'B',20,'Delhi')
insert into studentp values(3,'C',30,'Gurgaon')
select * from studentp

create function info2(@roll int)Returns @myTable TABLE
(
roll int,
name varchar,
address varchar(10)
)
AS
BEGIN
INSERT @myTable
Select roll,name,address
FROM studentp
RETURN
END

SELECT * FROM dbo.info2(1)

When I m calling the function it shows all the rows of the table,but i m passing roll as parameter to the function & i m passing roll Number 1,function has to showed the result of only Roll Number 1 ,then why all the rows are returned.

You should use where condition for this :)

INSERT @myTable
Select roll,name,address
FROM studentp
WHERE roll = @roll

Better you verify yourself the questions you ask before posting !