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.
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.
Comment