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,addre ss
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,addre ss
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,addre ss
FROM studentp
WHERE roll = @roll

Better you verify yourself the questions you ask before posting !